2

I'm stuck at trying to write the correct SQL code for retrieving and displaying all results from querying three tables. Simply put, I have three tables: A, B, and C. A and C are not connected to each other and must use B to get the referenced data that A needs from C. Below are simple examples of the tables:


Table Users
-----------
user_id     username
-------     ---------
1           bob
2           harry
3           jill

Table User_Emails
-----------------
email_id    user_id
--------    -------
1           1
2           1
3           2

Table Emails
------------
email_id    address
--------    --------------
1           bob-email1@test.com
2           bob-email2@test.com
3           harry-email1@test.com

In this example, the user_emails table holds an id that links an email address to a user account, letting a user have multiple email addresses. What I am trying to do is retrieve all the users and the email addresses for those users and if they do not have an email address just display a null value.

I cannot change the table as this is how it is set in stone. The database that is being used is Oracle 12c. Currently I have a cursor that grabs all the users and then loops through each user, checking it against the user_emails table. While this would work for a small result set, the reality is that there are thousands of records and I know this is inefficient, however I cannot figure out how to display the usernames and email addresses together along with null values for email addresses if the username doesn't have an id in the user_emails table. I asked the in-house DBA and didn't get anywhere, so I am asking for some SQL help from the gurus as this dummy (me) cannot figure it out.

Thank you for your time and help.

closed as off-topic by ypercubeᵀᴹ, mustaccio, Andriy M, Marian, Philᵀᴹ Sep 14 '16 at 15:35

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – ypercubeᵀᴹ, mustaccio, Andriy M, Marian, Philᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    The "man-in-the-middle" table has various names: join table, junction table, mapping table and many others. – Andriy M Sep 14 '16 at 7:15
4

This is a very simple query:

SELECT 
    u.username,
    e.address
FROM 
    Users  u
  LEFT JOIN User_Emails  ue 
    ON ue.user_id = u.user_id
  LEFT JOIN Emails  e 
    ON ue.email_id = e.email_id 
ORDER BY
    u.user_id, 
    e.address ;

You need the LEFT joins so users without emails are shown too (with NULL in the address).
You can add more columns in the SELECT list if you like, e.g. the u.user_id or the e.email_id.
The ORDER BY is also optional and can be changed according to your requirements.

  • yper, thank you for the response. I'm going to try that first thing in the morning and let you know. I've tried left joins before but couldn't get it to work, though I'm not that efficient at SQL so I'm sure it's an issue between the keyboard and chair. Thanks! – need a little help Sep 14 '16 at 1:30
  • yper - thank you for the help. After playing around with the fields and comparing the results, I found out what my problem was. I misunderstood how the sets are combined and used when applying inner join matches after the tables have been joined via an outer join. This helped, thanks. – need a little help Sep 15 '16 at 2:31

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