MySQL Forums
Forum List  »  InnoDB

Re: Dealing with Multiple MANY to MANY JOINs in Query
Posted by: Roland Bouman
Date: June 30, 2005 06:20PM

Not a transaction post. Anyway,

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

, c.lastname
, ' '
from contact c
left join contact_phone cp on = cp.contact_id
left join phone p on cp.phone_id =
left join contact_email cm on = cm.contact_id
left join email m on cm.email_id =
group by

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!

Options: ReplyQuote

Written By
Re: Dealing with Multiple MANY to MANY JOINs in Query
June 30, 2005 06:20PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.