MySQL Forums
Forum List  »  InnoDB

Dealing with Multiple MANY to MANY JOINs in Query
Posted by: Kevin Merritt
Date: May 07, 2005 07:53AM

I have a question regarding the best way to construct a certain JOIN query.
(I am using Mysql 4.0.24)

I have a database that holds contact information in the following way:

MAIN TABLES:
main_contact
main_address
main_phone
main_email

because (in this case) many people can be joined to the same email, address, or phone everything is done through joins

JOIN TABLES
join_address (joins MANY contacts to MANY address)
join_phone (joins MANY contacts to MANY phone)
Join_email (joins MANY contacts to MANY email)

I am trying to write a query to pull up one person's contact info.

SELECT (all_the_contact_info) FROM main_contact

LEFT JOIN join_address ON main_contact.key = join_address.link_contact
JOIN main_address ON main_address.key = join_address.link_address

LEFT JOIN join_phone ON main_contact.key = join_phone.link
JOIN main_phone ON main_phone.key = join_phone.link_phone

LEFT JOIN join_email ON main_contact.key = join_email.link
JOIN main_email ON main_email.key = join_email.link_email

WHERE main_contact.key = 1100 (the contact key of the person)

This is my best guess, but it seem that it is not the most optimized solution (if it even works!) since it is going to return a bunch of rows for just one contact. Ideally , I would love to be able to return just one row with the email, phones and addresses as part of one column which I could then split using a PHP function on the server end.

My only other solution is to run a separate query for each type of info I need to recieve, however, I don't know which is faster (or less taxing to the server), to run one complicates query or many simple ones?

Thanks for your advice on this?

-Kevin

Options: ReplyQuote


Subject
Views
Written By
Posted
Dealing with Multiple MANY to MANY JOINs in Query
3569
May 07, 2005 07:53AM


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.