Question:
I am having trouble getting the following type of join to work.
I have three tables: Application, College_hist, and School_Info.
- Application has student ID numbers that identify the person we want the info on
- College_hist has entries (0 or more) of previous school information. If they have entries, we want the one with a SEQ number of 1.
- School_info has the names of the schools which we want to show.
The following SQL will display the information if there exists a college_hist entry with SEQ of 1. I need it to show the student’s name even without any entries in college_hist. If I replace
a.SID = c.SID
with
a.SID *= c.SID
I get an error. How can I get an outer join with something I join with a third table?
Here’s the code:
Select a.SID, a.Name, s.School_Namefrom Application a, College_hist c, School_Info swhere a.SID = '555555555' anda.SID *= c.SID andc.SEQ = 1 ands.CID = c.SID
Answer:
I would do this in two steps:
- Produce the full join on
s.cID = c.sID
. - Apply the outer join to that result.