0

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?

Improve this question
2
  • 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.) 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. Commented May 24, 2020 at 8:54

1 Answer 1

Reset to default
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'
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.