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:

1. Produce the full join on s.cID = c.sID .
2. Apply the outer join to that result.