Proper SQL code for retrieving all rows from three tables, one of them is like a man-in-the-middle [closed]

Posted on

Question :

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

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.

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *