MySQL Forums
Forum List  »  Optimizer & Parser

Joining several multi-to-multi tables
Posted by: adambrill1986
Date: June 20, 2006 11:59AM

I have a question about MySQL theory. Assume that I have this setup:

table `user` has two field, `user_id`, `user_username`
table `email` has two fields, `user_id`, `email_value`
table `phone` has two fields, `user_id`, `phone_value`, `phone_type`

Now assume I do this query:

SELECT `user`.`user_username`, COUNT(`email_value`) AS `email_value`, COUNT(`phone_value`) AS `phone_value`
FROM `user`
LEFT JOIN `email` ON `email`.`user_id` = `user`.`user_id`
LEFT JOIN `phone` ON `phone`.`user_id` = `user`.`user_id`
GROUP BY `user`.`user_id`

Now assume there is a user with two email address and two phone numbers in the system. The query will return 4 for `email_value` and 4 for `phone_value`. How can I get accurate results from the query? I realize I could use something like DISTINCT, but if there would be a lot of users with a lot of email addresses and phone numbers, it would be very burdonsome on the server. If someone could give me some advice, I would appreciate it.

Options: ReplyQuote


Subject
Views
Written By
Posted
Joining several multi-to-multi tables
3633
June 20, 2006 11:59AM


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.