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 firstname.lastname@example.org 2 email@example.com 3 firstname.lastname@example.org
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.