Trouble with LEFT JOIN SQL Statement
-
Current we have a database that has two tables "order status" and "lead_free_certs". For each record in "order status" there may be 0 to many records in the "lead_free_certs" table. I'm trying to load a specific record from "order status" and if there is a matching record in "lead_free_certs" (based on project number), the most recent record in "lead_free_certs" based on the certificate number. I'm using VS2008 and I'm connecting to 2005 SQL Server. The SQL statement I have is:
SELECT * FROM [Order Status] LEFT JOIN Lead_Free_Certs ON [Order Status].[Project #] = Lead_Free_Certs.[Project Num] WHERE ([Order Status].[Project #] = " & projectnumber & ") ORDER BY Lead_Free_Certs.[Cert Num] DESC
The "projectnumber" variable is passed as an argument to the function. When I run this command no rows are returned. However if I want to load records only from the "order status" table then the following SQL statement works:
"SELECT * FROM [Order Status] WHERE [Project #] = " & projectnumber
So I know it is a problem with my LEFT JOIN statement. Any help would be greatly appreciated since this is the first time I'm trying to use JOIN statements.
-
Current we have a database that has two tables "order status" and "lead_free_certs". For each record in "order status" there may be 0 to many records in the "lead_free_certs" table. I'm trying to load a specific record from "order status" and if there is a matching record in "lead_free_certs" (based on project number), the most recent record in "lead_free_certs" based on the certificate number. I'm using VS2008 and I'm connecting to 2005 SQL Server. The SQL statement I have is:
SELECT * FROM [Order Status] LEFT JOIN Lead_Free_Certs ON [Order Status].[Project #] = Lead_Free_Certs.[Project Num] WHERE ([Order Status].[Project #] = " & projectnumber & ") ORDER BY Lead_Free_Certs.[Cert Num] DESC
The "projectnumber" variable is passed as an argument to the function. When I run this command no rows are returned. However if I want to load records only from the "order status" table then the following SQL statement works:
"SELECT * FROM [Order Status] WHERE [Project #] = " & projectnumber
So I know it is a problem with my LEFT JOIN statement. Any help would be greatly appreciated since this is the first time I'm trying to use JOIN statements.
Just a guess, but is it possible that the two columns of "Project #" and "Project Num" are different types and some implicit cast causes the JOIN to fail. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Just a guess, but is it possible that the two columns of "Project #" and "Project Num" are different types and some implicit cast causes the JOIN to fail. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
Didn't think of that; yes they are different types. Project Num is nvarchar(255) and Project # is int. Would it be better to convert them to the same type or is there a way to do the conversion in the SQL statement. There might be a problem with the conversion because some of the records in Lead_Free_Certs table are not strictly numerical. Some of the numbers have hyphens or slashes in them.
-
Just a guess, but is it possible that the two columns of "Project #" and "Project Num" are different types and some implicit cast causes the JOIN to fail. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
Okay I tried casting and it worked. The new statement that works is:
SELECT * FROM [Order Status] LEFT JOIN Lead_Free_Certs ON (CAST([Order Status].[Project #] AS nvarchar(255)) = Lead_Free_Certs.[Project Num]) WHERE ([Order Status].[Project #] = " & projectnumber & ") ORDER BY Lead_Free_Certs.[Cert Num] DESC
Thanks for the help!
-
Didn't think of that; yes they are different types. Project Num is nvarchar(255) and Project # is int. Would it be better to convert them to the same type or is there a way to do the conversion in the SQL statement. There might be a problem with the conversion because some of the records in Lead_Free_Certs table are not strictly numerical. Some of the numbers have hyphens or slashes in them.
Add an int column called Project # to the Lead_Free_Certs table and figure out how to populate it based upon the value from the Project Num column. But it sounds like you are trying to join two tables that were'nt to be joined together. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Okay I tried casting and it worked. The new statement that works is:
SELECT * FROM [Order Status] LEFT JOIN Lead_Free_Certs ON (CAST([Order Status].[Project #] AS nvarchar(255)) = Lead_Free_Certs.[Project Num]) WHERE ([Order Status].[Project #] = " & projectnumber & ") ORDER BY Lead_Free_Certs.[Cert Num] DESC
Thanks for the help!
You're welcome. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Didn't think of that; yes they are different types. Project Num is nvarchar(255) and Project # is int. Would it be better to convert them to the same type or is there a way to do the conversion in the SQL statement. There might be a problem with the conversion because some of the records in Lead_Free_Certs table are not strictly numerical. Some of the numbers have hyphens or slashes in them.
The LEFT OUTER JOIN or simply LEFT JOIN return all rows from the first table listed after the FROM clause or left of JOIN keyword , no matter if they have matches in the right table of the JOIN keyword. Syntax : FROM table1 LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name ; Check here lot of examples : http://www.w3resource.com/sql/joins/perform-a-left-join.php