3

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
Improve this question
4
  • I hope above normalization is only for example, otherwise new table for each column is useless :) Commented Apr 6, 2012 at 9:25
  • I know i can do it by join but i am getting 9 rows. So i just need 3 rows a result mentioned in my question Commented Apr 6, 2012 at 9:30
  • How do you know which email belongs to which phone number? Commented Apr 6, 2012 at 10:53
  • What if there were 4 email addresses and 2 phone numbers? What will be the expected output? Commented Apr 6, 2012 at 10:53

4 Answers 4

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I appreciate your answer but i dont have choice because i can not make any changes in the production database. All i have to play with mysql queries. if this is not possible to fetch data using mysql then definitely i will have to handle this at script level.
@neeraj: You can't handle that at the script level, because the facts you need aren't in the database. If Phonenumber.id and Email.id are autoincrement integers (a reasonable assumption on our part), there's no deterministic way to tell that '[email protected]' should be paired with '123456' instead of '123458'.
Can you please take a look at my another query at dba.stackexchange.com/questions/16173/… If there is a solution for that query then i think i can find solution for this question.
1

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?

Improve this answer

1 Comment

Thanks for your reply. I didnt use group_concat() because it return a result in a single string.
0

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
Improve this answer

10 Comments

I know i can do it by join but i am getting 9 rows. So i just need 3 rows a result mentioned in my question
Thanks for your reply but this is not a right query as i am still getting 9 rows. You first try to understand the question that how would i need the result then write the query and run
Your tables do not have a unique relation between emails and phone-numbers. If those email-adresses are different in each row, how should the query know that a certain phone-number relates to that? So it is possible to get 3 rows out of this query by using DISTINCT. But you should understand AND explain first what it is you're trying to retrieve and why it should result in 3 rows.
There is a relationship between user, email and phonenumber on the basis of userid of user table. Now I know distinct will give me unique rows but i want to get rows from each table (email and phonenumber) as a column. This is the very general scenario where different table is used to save multiple values of the base table. Do you want to say that this normalization can not work ??
Joining everything together will give you 9 rows because there is no relation between the first row of the email-adresses and the first row of the phone-numbers. Why does the first email-adress relate to the first phone-number. You will have to specify this in your model else it is indeed not properly normalized.
|
-1

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

Improve this answer

1 Comment

This is giving 9 rows. I need just 3 rows

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.