Two Where Caluses in a SELECT statement (parts of LEFT JOINs) 1

[Amesville]

Hi Folks

I think I know what's wrong with the query below, two WHERE clauses. But I need to do two different lookups on the Lookup Table which requires a where clause to identify the group I want to access (Table design is Group, code, desc). Any way I can fix this or get around that?

	SELECT Interchange.IntID, Town.TownName AS IntTown, AARRRCode.Railroad AS IntPrimaryRR, 
		AARRRCode_1.Railroad AS IntForeignRR, Lookup.LkDesc AS IntForeignRRDir, 
		Town_1.TownName AS IntNearestOnLineInt, lookup_1.LkDesc AS IntStagingArea 
		FROM (((((Interchange 
			LEFT JOIN Town ON Interchange.IntTown = Town.TownCallSign) 
			LEFT JOIN AARRRCode ON Interchange.IntPrimaryRR = AARRRCode.ReportMark) 
			LEFT JOIN AARRRCode AS AARRRCode_1 ON Interchange.IntForeignRR = AARRRCode_1.ReportMark) 
			LEFT JOIN Town AS Town_1 ON Interchange.IntNearestOnLineInt = Town_1.TownCallSign) 
			LEFT JOIN Lookup AS Lookup_1 ON Interchange.IntStagingarea = Lookup_1.LkCode WHERE Lookup_1.LkGroup = 'StagingArea')
			LEFT JOIN Lookup ON Interchange.IntForeignRRDir = Lookup.LkCode WHERE Lookup.LkGroup = 'Orientation'
			 
		ORDER BY Town.TownName, AARRRCode_1.Railroad;

It gives my an Incorrect Syntax error on the WHERE clause on the Lookup_1 line.

 

[gmmastros]

Change the WHERE to AND

SELECT Interchange.IntID, Town.TownName AS IntTown, AARRRCode.Railroad AS IntPrimaryRR, 
		AARRRCode_1.Railroad AS IntForeignRR, Lookup.LkDesc AS IntForeignRRDir, 
		Town_1.TownName AS IntNearestOnLineInt, lookup_1.LkDesc AS IntStagingArea 
		FROM (((((Interchange 
			LEFT JOIN Town ON Interchange.IntTown = Town.TownCallSign) 
			LEFT JOIN AARRRCode ON Interchange.IntPrimaryRR = AARRRCode.ReportMark) 
			LEFT JOIN AARRRCode AS AARRRCode_1 ON Interchange.IntForeignRR = AARRRCode_1.ReportMark) 
			LEFT JOIN Town AS Town_1 ON Interchange.IntNearestOnLineInt = Town_1.TownCallSign) 
			LEFT JOIN Lookup AS Lookup_1 ON Interchange.IntStagingarea = Lookup_1.LkCode AND Lookup_1.LkGroup = 'StagingArea')
			LEFT JOIN Lookup ON Interchange.IntForeignRRDir = Lookup.LkCode AND Lookup.LkGroup = 'Orientation'
			 
		ORDER BY Town.TownName, AARRRCode_1.Railroad;

This will have the same affect as a where clause, but the syntax will work.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Amesville]

Thank you! That totally did the trick.