Checking for values in same table

[EvansEd]

Can someone please help me with this...

I am trying to write an SQL statement for the following:
If REC_TYPE = '02' and LOT_NBR is null, look for
REC_TYPE = '03' with same DLVRY_KEY as REC_TYPE = '02' and load those records.

If there is no REC_TYPE = '03' for a particular DLVRY_KEY,
load REC_TYPE = '02' records for that same DLVRY_KEY.

DLVRY_KEY REC_TYPE LOT_NBR
____________________________
001 02 null
001 03 abc
001 03 efg


I tried the where exists, but that didn't help...any suggestions?

 

[Chao]

I think your query cannot be accomplished easily with one query. Have you try writing a stored procedure?

 

[teado]

A very quick stab for what I interpreted that you were looking for. HTH, or at least points in the right direction...

-- Get 02/null recs where an 03 rec doesn't exist
select * from table a
where rec_type = '02' and lot_nbr is null
and not exists (select 'x' from table b
where rec_type = '03'
and b.dlvry_key = a.dlvry_key
and ...)
UNION
-- Get 03 recs where an 02/null rec DOES exist
select * from table c
where rec_type = '03'
and exists (select 'x' from table d
where d.rec_type = '02'
and d.lot_nbr is null
and d.dlvry_key = c.dlvry_key)