SQL LEFT JOIN query
-
Hey all, I am still fairly knew to SQL query writing, and have searched to try and solve this by myself, however have had no luck! I am trying to write a query that returns information from SaleInformation and File_Info, with SaleInformation including all records that match a query and File_Info returning the matching File_Name(I have a relationship between them with PID the primary key in File_Info) if the PID is a match. However, my query is returning a result in every File_Name row return, even though I only have 3 entries in the File_Info PID column that match only 3 records in the Sale_information table. A gentle push in the right direction would be greatly appreciated! Oh, on a side note - If I change the query to "ON File_Info.PID = SaleInformation.PID" I get the 3 records that match returned. Here is the query I have at the moment: USE [C:\DATABASE\SALES.MDF] GO SELECT File_Info.File_Name, SaleInformation.PID, SaleInformation.Sale_date, SaleInformation.Sale_price FROM File_Info LEFT OUTER JOIN SaleInformation ON SaleInformation.Sale_price > 150000 It returns 183 rows. There are only 94 rows in the SaleInformation table and 3 in the File_Info table. I am very confused! Regards, Joe
-
Hey all, I am still fairly knew to SQL query writing, and have searched to try and solve this by myself, however have had no luck! I am trying to write a query that returns information from SaleInformation and File_Info, with SaleInformation including all records that match a query and File_Info returning the matching File_Name(I have a relationship between them with PID the primary key in File_Info) if the PID is a match. However, my query is returning a result in every File_Name row return, even though I only have 3 entries in the File_Info PID column that match only 3 records in the Sale_information table. A gentle push in the right direction would be greatly appreciated! Oh, on a side note - If I change the query to "ON File_Info.PID = SaleInformation.PID" I get the 3 records that match returned. Here is the query I have at the moment: USE [C:\DATABASE\SALES.MDF] GO SELECT File_Info.File_Name, SaleInformation.PID, SaleInformation.Sale_date, SaleInformation.Sale_price FROM File_Info LEFT OUTER JOIN SaleInformation ON SaleInformation.Sale_price > 150000 It returns 183 rows. There are only 94 rows in the SaleInformation table and 3 in the File_Info table. I am very confused! Regards, Joe
Joe Stansfield wrote:
I have a relationship between them with PID the primary key in File_Info
Perhaps you think that that will somehow automatically affect the join? It doesn't; you need to specify the relationship in the join.
-
Joe Stansfield wrote:
I have a relationship between them with PID the primary key in File_Info
Perhaps you think that that will somehow automatically affect the join? It doesn't; you need to specify the relationship in the join.
Ahh thanks! That is exactly what I thought would happen... Back to the old drawing board!
-
Hey all, I am still fairly knew to SQL query writing, and have searched to try and solve this by myself, however have had no luck! I am trying to write a query that returns information from SaleInformation and File_Info, with SaleInformation including all records that match a query and File_Info returning the matching File_Name(I have a relationship between them with PID the primary key in File_Info) if the PID is a match. However, my query is returning a result in every File_Name row return, even though I only have 3 entries in the File_Info PID column that match only 3 records in the Sale_information table. A gentle push in the right direction would be greatly appreciated! Oh, on a side note - If I change the query to "ON File_Info.PID = SaleInformation.PID" I get the 3 records that match returned. Here is the query I have at the moment: USE [C:\DATABASE\SALES.MDF] GO SELECT File_Info.File_Name, SaleInformation.PID, SaleInformation.Sale_date, SaleInformation.Sale_price FROM File_Info LEFT OUTER JOIN SaleInformation ON SaleInformation.Sale_price > 150000 It returns 183 rows. There are only 94 rows in the SaleInformation table and 3 in the File_Info table. I am very confused! Regards, Joe
As Piebald suggest, you need the relationship in the join. You can include the filter as well but the relationship must be there.
SELECT File_Info.File_Name, SaleInformation.PID, SaleInformation.Sale_date, SaleInformation.Sale_price
FROM File_Info LEFT OUTER JOIN SaleInformation ON File_Info.PID = SaleInformation.PID
and SaleInformation.Sale_price > 150000or
SELECT File_Info.File_Name, SaleInformation.PID, SaleInformation.Sale_date, SaleInformation.Sale_price
FROM File_Info LEFT OUTER JOIN SaleInformation ON File_Info.PID = SaleInformation.PID
WHERE SaleInformation.Sale_price > 150000Never underestimate the power of human stupidity RAH
-
As Piebald suggest, you need the relationship in the join. You can include the filter as well but the relationship must be there.
SELECT File_Info.File_Name, SaleInformation.PID, SaleInformation.Sale_date, SaleInformation.Sale_price
FROM File_Info LEFT OUTER JOIN SaleInformation ON File_Info.PID = SaleInformation.PID
and SaleInformation.Sale_price > 150000or
SELECT File_Info.File_Name, SaleInformation.PID, SaleInformation.Sale_date, SaleInformation.Sale_price
FROM File_Info LEFT OUTER JOIN SaleInformation ON File_Info.PID = SaleInformation.PID
WHERE SaleInformation.Sale_price > 150000Never underestimate the power of human stupidity RAH
Thanks! I had some trouble making it work - but it all came down to simply having the tables on the wrong sides of the LEFT OUTER JOIN. Switched them around and it is return as I wanted! Cheers for the assistance.
-
Thanks! I had some trouble making it work - but it all came down to simply having the tables on the wrong sides of the LEFT OUTER JOIN. Switched them around and it is return as I wanted! Cheers for the assistance.
-
Thanks again! Great article - definitely helped me understand.