Oracle Sql join two tables

Question

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        [email protected]        [email protected]    
12222   B        [email protected]    [email protected]

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?

Please in code questions give a minimal reproducible example--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. Isolate the first erroneous subexpression & its input & output. (Debugging fundamental.) — philipxy
– philipxy, Commented May 24, 2020 at 8:53
This is a faq. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See How to Ask & the voting arrow mouseover texts. — philipxy
– philipxy, Commented May 24, 2020 at 8:54

Gordon Linoff · Accepted Answer · 2020-05-23 19:37:18Z

1

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'

answered May 23, 2020 at 19:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle Sql join two tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related