Locating Customers with a certain settings

[timely]

I am trying to locat customers that have one product but not the other. I am sure this is not that hard but just not much time to play.

Example: Lets say that a company offers three types of insurance (health, life, and long term). For each product the customer has is another entry on a give table. Well, I want to find the customers that have health but do not have long term yet.

I tried the following with not luck. ctm_nbr is customer number.

create view fcm_view as
select *
from cirsub_m
where pub_cde = 'Longterm'
and RTE_CDE <> 'COMP'

create view jdt_view as
select *
from cirsub_m
where pub_cde = 'Life'
and ctg_id = 'JDTCAN'
and RTE_CDE <> 'COMP'

select a.*
from jdt_view a
left join fcm_view b on a.ctm_nbr <> b.ctm_nbr

also tried

select a.*
from jdt_view a
where not exists (select ctm_nbr from fcm_view b
where b.ctm_nbr = a.ctm_nbr)

Any help with be welcomed.

 

[ClaireHCM]

Try this

select a.*
from jdt_view a left join fcm_view b on a.ctm_nbr = b.ctm_nbr where b.ctm_nbr is null