Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Oct 8, 1999

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_Name
from Application a, College_hist c, School_Info s
where a.SID = '555555555' and
a.SID *= c.SID and
c.SEQ = 1 and
s.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.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date