devxlogo

Outer Join Problem

Outer Join Problem

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.
See also  Comparing different methods of testing your Infrastructure-as-Code
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist