Mysql normalization
I have three tables
USER TABLE userid username 1 alpha Email TABLE id userid email 1 1 alpha1@test.com 2 1 alpha2@test.com 3 1 alpha3@test.com Phonenumber TABLE id userid phonenumber 1 1 123456 2 1 123457 3 1 123458
How can I get below result using one query
userid username email phonenumber 1 alpha alpha1@test.com 123456 1 alpha alpha2@test.com 123457 1 alpha alpha3@test.com 123458
source to share
Start with a version of your data that is not normalized at all, and add additional, reasonable data so we can see how normalization works. (Let's say everyone has at least one email address and one phone number, just to avoid talking about mistakes.)
userid username email phonenumber
1 Alpha alpha1@test.com 123456
1 Alpha alpha2@test.com 123457
1 Alpha alpha3@test.com 123458
2 Beta beta1@test.com 234567
2 Beta beta2@test.com 234567 (2 email addresses, 1 phone)
3 Gamma gamma1@test.com 234678
3 Gamma gamma1@test.com 234679 (1 email address, 2 phones)
4 Alpha alpha32@test.com 345678 (2 people, #1 and #4, with same name)
If you look closely at this data, you will find that the only key is {email, phonenumber} .
This is why you are having trouble getting only three rows - that key is nowhere in your tables. This is what @ontrack said, saying, "Your spreadsheets don't have a unique relationship between emails and phone numbers."
By following the algorithm for determining candidate keys in any database tutorial, you will get the same thing. AFAIK, every database theory textbook has at least one algorithm for determining candidate keys.
Obviously, if you had a table with {email, phonenumber} as the key, you would only get 3 rows for userid 1.
source to share
I can't answer your problem, but have you considered using it group_concat()
?
SELECT userid, username, GROUP_CONCAT(DISTINCT email), GROUP_CONCAT(DISTINCT phonenumber)
FROM Email
LEFT JOIN Phonenumber USING (userid)
LEFT JOIN User USING (userid)
GROUP BY userid
It should give you this output:
userid username email phonenumber
1 alpha alpha1@test.com,alpha2@test.com,alpha3@test.com 123456,123457,123458
Perhaps this will solve your original problem?
source to share