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.

  • 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

This is a very simple query:

    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 
    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.

| improve this answer | |
  • 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.