CODEWITHSUNDEEP
sqlsql-serverselectleft-join
Jimbo Jones
Jimbo Jones

Reputation: 1002

Returning ID's from two other tables or null if no IDs found using using a left join SQL Server

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.

SELECT 
    T2.ProductID, FirstChild.SupplierID, SecondChild.AccountID
FROM 
    Products T2
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

Example of what I am trying to do

ProductID     SupplierID   AccountID
   1             5           2
   2             6           NULL
   3             NULL        NULL

Upvotes: 0

Views: 26

Answers (2)

MattR
MattR

Reputation: 1

If it's just showing duplicate records, wouldn't an inelegant solution just be to add distinct in the select line?

Upvotes: 0

Matt
Matt

Reputation: 14341

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.

Upvotes: 1

Related Questions