Joining several multi-to-multi tables
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.