Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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 the below result using a single query

userid username  email           phonenumber
1      alpha    alpha1@test.com 123456
1      alpha    alpha2@test.com 123457
1      alpha    alpha3@test.com 123458
share|improve this question
Why would email alpha1@test.com be related to number 123456 and not to 123457? Or to 123458 Or to all 3? Random choice? – ypercube Apr 6 '12 at 12:28
And what it has to do with normalization? Nothing. – ypercube Apr 6 '12 at 12:32
I just wanted to fetch rows from email and phonenumber table is a single query – neeraj Apr 6 '12 at 13:14
@ypercube Pairing does not matter in above result. All i just want to pull rows from email and phonenumber table using a single query. order of the records may differe. – neeraj Apr 6 '12 at 13:22
I think this is better on SO where you have cross-posted so I'm closing it here – Jack Douglas♦ Apr 6 '12 at 19:39

closed as off topic by Jack Douglas♦ Apr 6 '12 at 19:39

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined in the FAQ. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about closed questions here.

2 Answers

up vote 0 down vote

I hope this will work for you.

SELECT A.userid, A.username, B.email, C.phonenumber FROM USER A JOIN Email B on A.userid=B.userid JOIN Phonenumber C on A.userid=C.userid

share|improve this answer
Thanks for your reply but this query is giving me 9 rows where as i want to have just 3 rows. – neeraj Apr 6 '12 at 10:08
@Ashish, please try to limit your responses to one answer. In the future, if you have more information to add to a post, please just edit your existing answer. – BryceAtNetwork23 Apr 6 '12 at 11:22
up vote 0 down vote 
SELECT A.userid, A.username, B.email, C.phonenumber 
FROM USER A 
JOIN Email B on A.userid = B.userid 
JOIN Phonenumber C on B.userid = C.userid AND B.id = c.id

This will work.

share|improve this answer
The above query works fine if id of phonenumber table and email are same...Otherwise no result will come..I changed my id of phonenumber and run your query and didnt get any result...So you can not join B.id = C.id that is wrong – neeraj Apr 6 '12 at 11:05
Your anwer is again wrong. First thing is that you should have add "3" id for 'alpha3@test.com' in the above query and secondly as i said in my previous comment that if you change the id field values of Phonenumber table from 1,2,3 to something else like 7,8,9 then your query does not work. You can try it. Just change ids of Phonenumber and run your own query then you will find the result. – neeraj Apr 6 '12 at 11:51
Oh i see you have removed your previous answer where you had written all queries – neeraj Apr 6 '12 at 11:51

Not the answer you're looking for? Browse other questions tagged or ask your own question.