Hi

I have a simple query

select distinct
 bl_rec_ref
 ,freq_cd
 from hgmprty1
where(bl_rec_ref is not null and bl_rec_ref not like '%SCH%')

that produces the following output (part shown)

However what I need to show is only bl_rec_ref values where there is no corresponding freq_cd of QTLY.

Looking at the example data above Rows 45 and 46 have the same bl_rec_ref value with a MTHLY and a QTLY value in freq_cd so therefore I don't want this to appear in my output. Similarly with Rows 47 and 48 were the bl_rec_ref has both QTLY and MTHLY values so again should be excluded.

However looking at Rows 51 to 55 which has a bl_rec_ref only showing once for each value of MTHLY and no value for QTLY so these Rows are required in the output.

Can anyone help with this

Thanks

John

• Edited by wha59 11 hours 6 minutes ago no picture shown

Try

;with cte as (SELECT
 bl_rec_ref
 ,freq_cd
 from hgmprty1
where(bl_rec_ref is not null and bl_rec_ref not like '%SCH%'))

select * from cte C1 where not exists (select 1 from cte c2
where c2.bl_rec_ref = c1.bl_rec_ref and freq_cd = 'QTLY')

Hi

That works exactly as required, but can you explain the piece of code you supplied as always keen to learn and understand :)

John

EXISTS operator checks if there are rows satisfying the specified condition. So, in your case for each bl_rec_ref we're checking if there are QTLY rows. We need to exclude such bl_rec_ref rows.

Thanks for your assistance