Return row with column values or missing multiple rows

I have a SQL query as follows in latest version of SQL Server:

SELECT s.*  FROM Uom s  INNER JOIN catalogue l ON l.Uom = s.SAPUom  WHERE l.Id IN (3, 4)  ORDER BY (CASE WHEN s.compid IS NOT NULL OR s.supplierid IS NOT NULL THEN 1 ELSE 2 END) 

with an output like this:

enter image description here

If there is a CompId and SupplierId uom mapping in the table (row 1 and not row 2), the query should return that UOM else return the uom that is NULL.

Row 3 does not have a specified uom (compId/SupplierId) in the table and needs to be returned too.

Any help would be appreciated

Asked on August 30, 2020 in Sql.
Add Comment
2 Answer(s)

I suspect that you want one row per SAPUom. If so, you can use this trick:

select top (1) with ties s.* from Uom s inner join      catalogue l      on l.Uom = s.SAPUom  where l.Id in (3, 4)  order by row_number() over (partition by s.SAPUom                             order by (CASE WHEN s.compid is not null OR s.supplierid is not null THEN 1 ELSE 2 END)                            ); 
Answered on August 30, 2020.
Add Comment

To get the 3rd row I think you could use UNION ALL to add the rows from table Uom which do not have a corresponding row in the catalog.

SELECT s.*  FROM Uom s       INNER JOIN      catalogue l ON l.Uom = s.SAPUom  WHERE l.Id IN (3, 4))  union all SELECT s.*  FROM Uom s  WHERE not exists (select 1 from catalogue c where c.Uom = s.SAPUom); 
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.