I have two tables:

Table1

And table 2

I want to write a SELECT that display if the WIPS on table 1 already exists on table 2, if not, display a respective message (keeping the Table 2 message, i.e. pending or received and adding a message 'Not Received' if the WIP doesn't exist on Table 2), so, with the columns from my example:

Right now I've tried the following query:

select 
    t.WIP, t.TimeStamp, t.Operation 
from 
    Table1 w
join 
    Table2 t on t.WIP = w.WIP
union
select 
    w.WIP, w.DateaReleased, 'Not Received' operation 
from 
    Table1 w
left join 
    Table2 t on t.WIP = w.WIP

And it kind of works, but it duplicates the records:

CodePudding user response：

You can use left join and case, like below

select t1.wip1 as wipr, t2.TimeStamp2, 
       case when t2.wip2 is null then 'NOT RECEIVED' else t2.status2 end as status
from Table1 t1
left join Table2 t2 on t1.wip1 = t2.wip2

CodePudding user response：

Just an alternative to @Farshid Shekari 's answer.

You can also use Coalesce:

select t1.wip1 as wipr
      ,t1.TimeStamp1 as timestampr
      ,coalesce(t2.status2, 'not received')
from Table1 t1
left join Table2 t2 on t1.wip1 = t2.wip2