3

I have three tables like this:

Person

id | name | location ----------------------- 1 | ABC | Location1 2 | XYZ | Location2

Contact

id | type | contact --------------------------- 1 | website | abc.com 2 | email | [email protected] 3 | website | xyz.com 4 | email | [email protected]

PersonContact

Person_id | Contact_id ----------------------- 1 | 1 1 | 2 2 | 3 2 | 4

I want to get result something like this:

id | name | website | email ---------------------------------- 1 | ABC | abc.com | [email protected] 2 | XYZ | xyz.com | [email protected]
Improve this question
4
  • 2
    What have you tried? What worked? What didn't? Commented Feb 9, 2012 at 19:15
  • 4
    What have you tried? This is really easy to do..I would feel bad if I just gave you the answer straight up. Commented Feb 9, 2012 at 19:15
  • possible duplicate of mysql - query three tables Commented Feb 9, 2012 at 19:27
  • 2
    I'm not sure this is as obvious as you guys are making it out to be. He basically wants to transpose rows into columns. Commented Feb 9, 2012 at 19:45

3 Answers 3

Reset to default
3

There are several ways to do this, but here is an easy one for MySQL

SELECT p.id, p.name, MAX(IF(c.type = 'website', c.contact, NULL)) AS 'website', MAX(IF(c.type = 'email', c.contact, NULL)) AS 'email' FROM Person p INNER JOIN PersonContact pc ON p.id = pc.Person_ID INNER JOIN Contact c ON pc.contact_id = c.id GROUP BY p.id
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

It's interesting how complex that query would be without using if statements. It's actually one left join per column! Great work @Ben!

select p.id, p.name, max(website) as website, max(email) as email from person p join personContact pc on p.id = pc.person_id join contact c on pc.contact_id = c.id left join ( select c.id as cid, c.contact as website from personContact pc join contact c on pc.contact_id = c.id where c.type = 'website' ) as WebsiteCol on c.id = WebsiteCol.cid left join ( select c.id as cid, c.contact as email from personContact pc join contact c on pc.contact_id = c.id where c.type = 'email' ) as EmailCol on c.id = EmailCol.cid group by p.id, p.name
Improve this answer

Comments

-1 
SELECT p.id, p.name, c.contact FROM Person p, PersonContact pc , Contact c WHERE p.id = pc.Person_id AND pc.Contact_id= c.id
Improve this answer

1 Comment

that doesn't give the result that the OP requested

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.