Oracle Sql join two tables

I have a query joining two tables.

RESULT_TABLE:  
ID, TEST_RESULT , TEST_STATUS

EMAIL_TABLE:  
ID, EMAIL_TYPE, EMAIL_ADDRESS

The RESULT_TABLE will typically have only one result per student. A student will usually have two email addresses in the EMAIL_TABLE, one of type ‘Personal’, and one of type ‘Student’.
I need a query that joins the student’s result and the two email addresses into one row.

My Query uses two instances of EMAIL_TABLE to do this:

select r.id, r.result, e1.email_address "PERSONAL", e2.email_address "STUDENT"  
from   result_table r, email_table e1, email_table e2 
where  r.test_status = 'graded' 
and    e1.id = r.id  
and    e2.id = r.id  
and    e1.email_type ='Personal'  
and    e2.email_type = 'Student'

Sample result:

ID      RESULT   PERSONAL                    STUDENT          
------- -------- --------------------------- ------------------------- 
12345   A        me@personalemail.com        ima@studentemail.edu    
12222   B        number2@anotheremail.com    youra@studentemail.edu

This query works in most cases, displaying the result and both types of email addresses for the student.

The problem is, if one of the email_types is missing from the email_table then the entire result is not found. I need it to display a result for each student even if one or both email addresses do not exist in the email_table.

What is the proper way to do this join?

Answer

Learn to use proper, explicit, standard, readable JOIN syntax!

You can use two left joins:

select r.id, r.result, ep.email_address as personal, e2.email_address as student
from result_table r left join
     email_table ep
     on ep.id = r.id and ep.email_type ='Personal' left join
     email_table et2
     on es.id = r.id and es.email_type = 'Student'

Advertisement

Answer