Trouble wih Left Joins 1

[Amesville]

Hi Folks

I'n just starting out using SQL Server, though I've been using Oracle for many years. The syntax is tripping me up a lot. I have written the following query that is supposed to do a lookup on one table and get code to description translations from two others (Lookup and Siding) using Left Joins:

SELECT Ind.IndID, Ind.Active, Ind.UseRec, Ind.Selected, Lookup.LkDesc AS Shiprecv, Ind.Commodity, Ind.Cartype, Siding.SidingNotes AS Siding, Ind.Allocated, Ind.Notes FROM Industry AS Ind WHERE Ind.Active = 'X' AND LEFT JOIN Lookup ON Ind.ShipRecv = Lookup.LkCode WHERE Lookup.LkGroup = 'ShipRecv' AND LEFT JOIN Siding ON Ind.Siding = Siding.SidingID;

But this keeps throwing an error when I attempt to parse it on SQL Server Management Stidio. The error text is not particularly helpful. Can anyone spot what I'm doing wrong and point it out to me?

Thanks

Amesville

 

[gmmastros]

I have zero experience with Oracle, but I would be surprised if this query would work. It certainly won't parse in SQL server.

The problem is that you are mixing your where clause criteria with your join criteria. Normally, a left join query looks like this:

[tt]
Select (Columns)
From TableX
Left Join TableY
On TableX.Column = TableY.Column
Where (Additional criteria here)
[/tt]

Reworking your query:

SELECT Ind.IndID, 
       Ind.Active, 
       Ind.UseRec, 
       Ind.Selected, 
       Lookup.LkDesc AS Shiprecv, 
       Ind.Commodity, 
       Ind.Cartype, 
       Siding.SidingNotes AS Siding, 
       Ind.Allocated, 
       Ind.Notes 
FROM   Industry AS Ind 
       LEFT JOIN Lookup 
         ON Ind.ShipRecv = Lookup.LkCode
         [!]And Lookup.LkGroup = 'ShipRecv'[/!]
       LEFT JOIN Siding 
         ON Ind.Siding = Siding.SidingID
WHERE Ind.Active = 'X'

There are a couple things you should notice. First, All of the joins are listed together. The where clause criteria is at the end (if you had an order by clause, that would go after the WHERE clause).

Lastly, take a look at the part highlighted in red above. Normally, a condition like this would be in the where clause. If that condition was in the where clause, this query would likely return different data. You see, this query should return all the data from the Ind table (where active = x). If there are any matching rows in the Lookup table, that row should be returned. If there are no matching rows in the Lookup table, then NULL will be returned for the columns. If you had the condition for LKGroup = 'ShipRecv' in the where clause, the row would be filtered out. If you have the condition in the join clause, you will still get the rows with NULLS.



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

 

[Amesville]

Thank you George. You syntax makes it a lot easier to understand. I've now changed the query to more closely match yours:

SELECT Ind.IndID, Ind.Active, Ind.UseRec, Ind.Selected,  Lookup.LkDesc As ShipRecv, Ind.Commodity, Ind.Cartype, Siding.SidingNotes AS Siding,Ind.Allocated, Ind.Notes FROM Industry Ind 
LEFT OUTER JOIN Siding ON Ind.ShipRecv = CAST(Siding.SidingID as varchar(10))
LEFT OUTER JOIN Lookup ON CAST(Ind.Siding as varchar(50)) = Lookup.LkCode AND Lookup.LkGroup = 'ShipRecv'
WHERE Ind.Active = 'X';

It now runs but the lookups dpn't return any data, all I get in the two fields are Nulls, all up and down. I had to use the CAST statements as Management Studio would not to implicit conversions of the data types - I'm not entirely sure I've done them properly. However, it parses cleanly.

Appreciate the help very much, hoping this isn't too big of a problem.

 

[Amesville]

George, never mind. I fixed the problem - somehow in the process of re-writing the query and adding the CAST function I somehow tried to campare the wrong fields. I corrected the problem (proper lookups in the Joins) and now it works swimmingly. Thank you for you help.

Craig

Amesville

 

[gmmastros]

Craig,

Can you explain why you needed to cast the data?

[tt]ON Ind.ShipRecv = CAST(Siding.SidingID as varchar(10))[/tt]

I suspect that Siding.SidingId is probably an integer column and ind.ShipRecv is probably a varchar column. When SQL Server compares data in separate columns, it uses data type precedence.

http://msdn.microsoft.com/en-us/library/ms190309.aspx

Basically, SQL Server will try to convert varchar's to integers. If you have data in the table that cannot be converted, you will get an error. By casting that data to a varchar, you are forcing string comparisons instead of number comparisons. Of course, this works, but it can also cause a performance problem.

You see, there is likely to be indexes on your tables that include the columns that you are joining on. By using a cast in the join condition, you are effectively preventing SQL Server from using the index, which will cause a performance problem. You may not notice any performance problems now based on the size of your tables, but as the number of rows increase, the performance problems are likely to cause you problems.

With SQL Server, there is a trick you can use that will cause an index to be used while still making sure that the data is compared as a string (instead of a number). Specifically, you can create a computed column in the table, and then create an index on the computed column. There will be a slight overhead caused by this during inserts, updates and deletes because SQL Server will need to maintain another index on the table, but your select query performance will improve.

If you are interested in learning about this technique, please let me know and I will explain it in detail.

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

 

[Amesville]

George, thanks very much - I was not aware of the nuts and bolts of how that worked with the CAST function. As it turns out the need for the CAST was due to the substitution error I made - I was trying to compare the code value in one table to the description value in the lookup table instead of the code value as I should have been. When I realized my error there was no need for the CAST statement so it was removed. The query is now working very well, and I've even managed to get it into a stored procedure.

I'm very glad you told me about the computed result field though, I will note that down and keep an eye out for it in the future. Now that I've proken the ice a bit with this query I'm starting to see there are more commonalities between PL-SQL and T-SQL than I thought. Dabbling in Access before this really messed me up and now I'm trying to unlearn some pre-conceived notions about SQL Server I had come in with.

Thanks again for your assistance!

Craig

Amesville