Optimize query with similar fields I have the below schema, fields owner_id,billing_id,tech_id,admin_id on carts table are pointing to contacts table. As you can see from the query result I have to JOIN for each field .Is there a way to avoid? 



SELECT c.domain, c.period, co.name AS owner, co1.name AS admin, co2.name AS tech, co3.name AS billing, h.nameserver
FROM carts c
LEFT JOIN contacts co ON c.owner_id = co.id
LEFT JOIN contacts co1 ON c.admin_id = co1.id
LEFT JOIN contacts co2 ON c.tech_id = co2.id
LEFT JOIN contacts co3 ON c.billing_id = co3.id
LEFT JOIN HOSTS h ON c.host_id = h.id
WHERE c.user_id =1
You can get the same results with just one join and pivoting . 

SELECT c.domain, c.period, h.nameserver,
MAX(CASE WHEN co.id=c.owner_id THEN co.name END) AS owner, 
MAX(CASE WHEN co.id=c.admin_id THEN co.name END) AS admin,
MAX(CASE WHEN co.id=c.tech_id THEN co.name END) AS tech, 
MAX(CASE WHEN co.id=c.billing_id THEN co.name END)  AS billing
FROM carts c
LEFT JOIN contacts co ON co.id IN (c.owner_id , c.admin_id,c.tech_id,c.billing_id)

LEFT JOIN HOSTS h ON c.host_id = h.id
WHERE c.user_id =1
GROUP BY c.domain,c.period,h.nameserver


However, check execution plan for both versions, sometimes multiple joins may be faster than such approach.