Re: Dealing with Multiple MANY to MANY JOINs in Query

[Roland Bouman]

Not a transaction post. Anyway,

You can do this - and in one row per contact! - using that wonderfull feature GROUP_CONCAT (manual 12.9):

select c.id
,group_concat(
distinct
c.firstname
, c.lastname
)
,group_concat(
distinct
cp.type
, ' '
, p.phone
)
,group_concat(
distinct
m.email
)
from contact c
left join contact_phone cp on c.id = cp.contact_id
left join phone p on cp.phone_id = p.id
left join contact_email cm on c.id = cm.contact_id
left join email m on cm.email_id = m.id
group by c.id

Just make sure you group by on whatever columns define a single contact, and create GROUP_CONCAT(DISTINCT ) expressions for each group of related contact detail fields.
I think it entirely depends on how you want to access the data from your php script if it is wise to use this type op query for this kind of problem. If you need a presentation form that closely resembles the query output, I would use it.
If that is not certain yet, or if you want to be flexible in your presentation, it is better to retrieve the data in as pure and simple form as possible, and let the PHP script put it together the way you want to. If you are worrying about consuming resources and generating network traffic from the database to the httpserver, you could consider using a stored procedure to do all the work in a single call.

best regards!