outer join question

[myatia]

Hi,

I have three tables, Instructor, InstructorZipCode, and InstructorClassification, that I want to join together. All instructors have a zip code, but some don't have a classification. I want to get all the instructors within n miles of a zip code, regardless of whether they have a classification. I have the math worked out to do that, I just need help on the join. I've tried the following

SELECT *

FROM (SELECT * FROM Instructor, InstructorZipCode WHERE (math crap) AS InstructorTable) 
LEFT OUTER JOIN Classification

WHERE InstructorTable.ClassificationID = Classification.ClassificationID

but that doesn't work (I get an error in the from clause). I think I have the right idea; I'm just not sure about the syntax (then again, maybe not . If anyone could offer me some pointers, I'd really appreciate it. Thanks,

Misty

 

[Denaeghel]

I think you forgot the ON statement

SELECT *
FROM (SELECT * FROM Instructor, InstructorZipCode WHERE (math crap)) AS InstructorTable
LEFT OUTER JOIN Classification
ON InstructorTable.ClassificationID = Classification.ClassificationID

Perhaps this will help

karel.denaeghel@barco.com

 

[myatia]

Thanks! That change, along with moving the "AS InstructorTable" outside of the parantheses, as shown below, made it work. I appreciate your help.

misty

SELECT *
		
FROM	

(
   SELECT  *
   FROM    Instructor, InstructorZipCode
   WHERE  (math stuff)
) AS InstructorTable
		
LEFT OUTER JOIN Classification
		
ON Classification.ClassificationID = InstructorTable.ClassificationID