CODEWITHSUNDEEP
sql-server
Dav1
Dav1

Reputation: 11

Join with conditions

emphasized textI have 2 tables. First table have 3 columns namely SupplierId ,SupplierDetailID and entityid and second table has 2 column namely SupplierId and SupplierDetailID. In first table there are more than one combination of SUPPLIERID and Supplierdetailid due more than one entity id..I want to form left join on supplier Id field in such a way that only when suppliersDetailID is blank in first table it should take max value of SupplierDetailID from second tables for that particular SupplierId. Formed query will give first_table.supplierID,if first_table SupplierDetailID is blank then max of second table SupplierDetailID for particular supplierId,first_table entityid. enter image description here

Upvotes: 0

Views: 69

Answers (1)

gongsun
gongsun

Reputation: 544

This SQL Query will work for you.

SELECT s1.SupplierID,
       ISNULL(s1.SupplierDetailID, MAX(s2.SupplierDetailID)) AS SupplierDetailID
FROM s1
    LEFT JOIN s2
        ON s1.SupplierID = s2.SupplierID
GROUP BY 
         s1.SupplierID,s1.SupplierDetailID;

s1 is first supplier table.

s2 is second supplier table.

Upvotes: 1

Related Questions