I am wondering if someone could hep me. I am trying to make a join on two tables and return an id if an id is there but if there is no id return null but still return the row for that product and not ignore it. My query below returns twice the amount the records to which I can not figure out why.
T2.ProductID, FirstChild.SupplierID, SecondChild.AccountID
LEFT OUTER JOIN
SELECT TOP(1) SupplierID, Reference,CompanyID, Row_Number() OVER (Partition By SupplierID Order By SupplierID) AS RowNo FROM Suppliers
FirstChild ON T2.SupplierReference = FirstChild.Reference AND RowNo = 1AND FirstChild.CompanyID =T2.CompanyID
LEFT OUTER JOIN
SELECT TOP(1) AccountID, SageKey,CompanyID, Row_Number() OVER (Partition By AccountID Order By AccountID) AS RowNo2 FROM Accounts
SecondChild ON T2.ProductAccountReference = SecondChild.Reference AND RowNo2 = 1 AND SecondChild.CompanyID =T2.CompanyID
ProductID SupplierID AccountID
1 5 2
2 6 NULL
3 NULL NULL
OUTER APPLY and ditching the ROW_NUMBER Seems like a better choice here:
SELECT p.ProductId ,FirstChild.SupplierId ,SecondChild.AccountId FROM Products p OUTER APPLY (SELECT TOP (1) s.SupplierId FROM Suppliers s WHERE p.SupplierReference = s.SupplierReference AND p.CompanyId = s.CompanyId ORDER BY s.SupplierId ) FirstChild OUTER APPLY (SELECT TOP (1) a.AccountId FROM Accounts WHERE p.ProductAccountReference = a.Reference AND p.CompanyId = a.CompanyId ORDER BY a.AccountID ) SecondChild
The way your query is written above there is no correlation for the derived tables. Which means you would always get what ever SupplierId SQL chooses based on optimization and if that doesn't happen to always be Row1 you wont get the value. You need to relate your Table and select top 1, adding an ORDER BY in your derived table is like identifying the row number you want.