Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

enter image description here

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

enter image description here

share|improve this question
    
You are only selecting data for one user so it should be pretty fast. Why do you want to avoid the 4 joins? –  ypercube Oct 24 '13 at 11:41
    
I'm asking if there is a better way to query this result. I just want to improve my SQL skills.Is there a for loop or a recursive way or a declarative trick to skip this? –  yannis Oct 25 '13 at 6:22
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
I can't figure out how is this working. I see you use MAX() how you replace it with a co.name shouldn't it be a number? –  yannis Nov 13 '13 at 14:41
    
it's just pivoting (other RDMS support PIVOT keyword to do the same, you can look on stackoverflow.com/questions/7674786/mysql-pivot-table for couple more examples). CASE inside MAX has default ELSE NULL clause. –  a1ex07 Nov 13 '13 at 14:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.