Reputation: 9205
I have three tables
USER TABLE
userid username
1 alpha
Email TABLE
id userid email
1 1 [email protected]
2 1 [email protected]
3 1 [email protected]
Phonenumber TABLE
id userid phonenumber
1 1 123456
2 1 123457
3 1 123458
How can i get the below result using a single query
userid username email phonenumber
1 alpha [email protected] 123456
1 alpha [email protected] 123457
1 alpha [email protected] 123458
Upvotes: 3
Views: 287
Reputation: 95771
Let's start with a version of your data that's not normalized at all, and add some additional, reasonable data so we can see how normalization works here. (Assumes everyone has at least one email address and one phone number, simply to avoid talking about nulls.)
userid username email phonenumber
1 Alpha [email protected] 123456
1 Alpha [email protected] 123457
1 Alpha [email protected] 123458
2 Beta [email protected] 234567
2 Beta [email protected] 234567 (2 email addresses, 1 phone)
3 Gamma [email protected] 234678
3 Gamma [email protected] 234679 (1 email address, 2 phones)
4 Alpha [email protected] 345678 (2 people, #1 and #4, with same name)
If you look closely at that data, you'll find that the only key is {email, phonenumber}.
That's why you're having trouble getting only three rows--that key is nowhere in your tables. This is what @ontrack was getting at by saying, "Your tables do not have a unique relation between emails and phone-numbers."
Following the algorithm for determining candidate keys in any database textbook will give you the same thing. AFAIK, every textbook on database theory has at least one algorithm for determining candidate keys.
Obviously, if you had a table that had {email, phonenumber} as the key, you'd get only 3 rows for userid 1.
Upvotes: 2
Reputation: 4446
I can't answer your problem, but have you considered using 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 result:
userid username email phonenumber
1 alpha [email protected],[email protected],[email protected] 123456,123457,123458
Maybe this will solve your original problem?
Upvotes: 1
Reputation: 3450
SELECT userid, username, email, phonenumber FROM Email LEFT JOIN Phonenumber USING (userid) LEFT JOIN User USING (userid)
Upvotes: -1
Reputation:
Simply. By joining your tables.
Try below:
SELECT u.userid,u.username,e.email,p.phonenumber
FROM User as u LEFT JOIN Email as e on u.userid=e.userid
LEFT JOIN Phonenumber as p on u.userid=p.userid
Upvotes: 0