How to SELECT from a table if no data exists in another

Question

This should be simple. I don't know why I feel stumped.

I have two similar tables. Let's say they each have just 2 columns: PartNumber, and Order.

In a single statement, I want to select the PartNumbers from WorkItemPartsFiltered where Order = ABC, but if none exist, I want to select them from WorkItemParts where Order = ABC

--WorkItemParts
PartNumber | Order
123        | ABC
456        | ABC
789        | ABC
012        | ABC
123        | DEF
456        | DEF
389        | GHI


--WorkItemPartsFiltered
PartNumber | Order
123        | ABC
456        | ABC
789        | ABC
456        | DEF
389        | GHI

score 2 · Answer 1 · answered Apr 24 '15 at 14:51

2

SELECT [Order]
      , COALESCE( WorkItemParts.PartNumber
                      ,WorkItemPartsFiltered.PartNumber) AS PartNumber
FROM WorkItemParts
LEFT JOIN WorkItemPartsFiltered 
ON WorkItemPartsFiltered.[Order] = WorkItemParts.[Order]

answered Apr 24 '15 at 14:51

M.Ali

67,945
13
101
127

score 2 · Answer 2 · edited Apr 24 '15 at 14:56

2

You can use coalesce, combined with a left join:

select
coalesce (WIPF.PartNumber, WIP.PartNumber)
from
WorkItemParts WIP
left join WOrkItemPartsFiltered WIPF
  on wip.order = wipf.order

edited Apr 24 '15 at 14:56

Micha Wiedenmann

19,979
21
92
137

answered Apr 24 '15 at 14:53

Andrew

8,445
3
28
46

score 1 · Accepted Answer · answered Apr 24 '15 at 14:48

You must use a case Statement with a JOIN like this:

SELECT Order, CASE WHEN WorkItemParts.PartNumber is null 
  THEN WorkItemPartsFiltered.PartNumber
  ELSE WorkItemParts.PartNumber END
FROM WorkItemParts
LEFT JOIN WorkItemPartsFiltered 
         ON (WorkItemPartsFiltered.Order = WorkItemParts.Order)

score 1 · Answer 4 · answered Apr 24 '15 at 14:55

You can also write as:

if not exists (
select *
from WorkItemPartsFiltered 
where [Order] = 'KLM')
begin
    select PartNumber,[Order] 
    from WorkItemParts
    where [Order] = 'KLM'
end 
else
begin
    select PartNumber,[Order] 
    from WorkItemPartsFiltered
    where [Order] = 'KLM'    
end

DEMO

score 1 · Answer 5 · answered Apr 24 '15 at 15:06

Yet another way to do this:

Create Table dbo.partsfiltered
(
PartNumber int,
OrderNum varchar(5),
SourceTable varchar(20)

);
Insert into partsfiltered
Values (123,'abc','filtered'), (678,'ghi','filtered');

Create Table dbo.parts
(
PartNumber int,
OrderNum varchar(5),
SourceTable varchar(20)
);
Insert into parts 
Values (123,'abc','full'), (345,'def','full'), (678,'ghi','full');


with cte as 
(
        Select  PartNumber,
                OrderNum,
                SourceTable,
                ROW_NUMBER() Over (Partition By PartNumber order by SourceTable) as [SourceTableRank]
        From
        (
        select * From partsfiltered
        union 
        select * From parts
        )a

)
Select PartNumber, OrderNum, SourceTable
From cte
where SourceTableRank = 1;

SQLFiddle here

How to SELECT from a table if no data exists in another

5 Answers5