CODEWITHSUNDEEP
mysqlnormalization
Neeraj
Neeraj

Reputation: 9205

Mysql Normalization

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

Answers (4)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

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

barsju
barsju

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

nikhil500
nikhil500

Reputation: 3450

SELECT userid, username, email, phonenumber FROM Email LEFT JOIN Phonenumber USING (userid) LEFT JOIN User USING (userid)

Upvotes: -1

user319198
user319198

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

Related Questions