CODEWITHSUNDEEP
sqlsql-server
Kevin
Kevin

Reputation: 80

Return row with column values or missing

I have a SQL query as follows in latest version MS-SQL:

SELECT s.* FROM catalogue l INNER JOIN uom s ON s.SAPUom = l.Uom AND l.Id = 5

with an output as follows

enter image description here

If there is a CompId and SupplierId uom mapping in the table (row 2) bring back that one else bring back the uom that is NULL (row 1)

Any help would be appreciated

Upvotes: 1

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

If you want one row, then one method uses ORDER BY:

SELECT TOP (1) s.*
FROM catalogue l INNER JOIN 
     uom s
     ON s.SAPUom = l.Uom AND l.Id = 5
ORDER BY (CASE WHEN compid is not null OR supplierid is not null THEN 1 ELSE 2 END);

Upvotes: 1

Related Questions