Hi,
I'm trying to generate a list of specific constituents (ie, members) that do not have a specific combination of constituencies, but can't seem to get either the "Not in" or "Does Not Have" to return correct results.
Here's my query...can anyone tell me what I'm missing or what I'm doing wrong?
Select Distinct a.customer_no
From V_CUSTOMER_WITH_PRIMARY_GROUP a WITH (NOLOCK)
JOIN (Select a1.customer_no From vxs_const_cust a1 WITH (NOLOCK) Where a1.constituency not in (6) and a1.constituency not in (7)) as e ON e.customer_no = a.customer_no
JOIN (Select a1.customer_no From vxs_cust_membership a1 WITH (NOLOCK) Where a1.memb_org_no in (1)) as f ON f.customer_no = a.customer_no
Where IsNull(a.inactive, 1) = 1
Thanks!
Ruth
The query you posted looks as if it may return Cartesian Product. Using semi-joins to test for existance can get around this. You might try something like:
select distinct
cpg.customer_no
from
V_CUSTOMER_WITH_PRIMARY_GROUP as cpg
where
isnull(cpg.inactive, 1) = 1
and exists (
select
0
vxs_const_cust as cc
cc.constituency not in (6,7)
and cc.customer_no = cpg.customer_no
)
vxs_cust_membership as cm
cm.memb_org_no = 1
and cm.customer_no = cpg.customer_no