So I have a table which has a composite primary key for SHIP# & REF#. Each SHIP# has two codes for REF# which are BM and PO. BM field is mandatory but the PO field only gets populated when the user actually inputs something. So, a basic select would display something like this:
SHIP# REF# VALUE
000002 BM 20001836
000002 PO 020
000003 BM 20001834
000003 PO 8-694
000004 BM 20001835
Now, you will notice that shipment 000004 has only BM and no PO.
I want to display all shipments with PO's values. So, if the PO value is empty or doesn't exist like in case '000004', it should simply put '-'. Since the BM is mandatory you'll have to get all records where BM exists but display value of the PO field.
So, the output should be:
SHIP# REF# VALUE
000002 PO 020
000003 PO 8-694
000004 PO -
Let me know if you need more clarifications. Thanks.
An outer join against itself can do the job too. For example:
select a.ship, 'PO' as ref, coalesce(b.value, '-') as value
from t a
left join t b on b.ship = a.ship and b.ref = 'PO'
where a.ref = 'BM'
Result:
SHIP REF VALUE
------ --- -----
000002 PO 020
000003 PO 8-694
000004 PO -
See running example at db<>fiddle.
EDIT - Find only BMs with no PO.
You can use the same query and add the extra predicate and b.ship is null in it, as in:
select a.ship, 'PO' as ref, coalesce(b.value, '-') as value
from t a
left join t b on b.ship = a.ship and b.ref = 'PO'
where a.ref = 'BM'
and b.ship is null
Result:
SHIP REF VALUE
------- ---- -----
000004 PO -
See running example at db<>fiddle.