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:
If there is a
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
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) );
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);