SQL Query using INTERSECT or EXISTS??

[rmelnyck]

I have 2 queries (queries 1 and 2 below) and I want only the rows that are common to each. Then I want to exclude from that result the rows I get from queries 3 and 4.




Query #1:
select distinct(a.id_num)
from v_aldr a,v_gift g
where a.id_num = g.id_num
AND (a.org_typ_1_cod = '1') and (a.acct_stat_cod IN ('0','1')) and (g.fin_dsgn_cod IN ('G','P','MG','MP'))
and (g.post_dat BETWEEN '19400101' AND '20010531')
and (g.devl_org_unit_cod LIKE '243%'
OR g.devl_org_unit_cod LIKE '225%'
OR g.devl_org_unit_cod LIKE '230%'
OR g.devl_org_unit_cod LIKE '250%'
OR g.devl_org_unit_cod LIKE '235%'
OR g.devl_org_unit_cod LIKE '240%'
OR g.devl_org_unit_cod LIKE '255%'
OR g.devl_org_unit_cod LIKE '265%'
OR g.devl_org_unit_cod = '3105100'
OR g.devl_org_unit_cod LIKE '245%'

Query #2:
select distinct(a.id_num)
from v_aldr a,v_gift g
where a.id_num = g.id_num
and (g.ACCT_NUM = '440301')
AND (g.FIN_DSGN_COD IN ('G', 'P', 'MG', 'MP'))
AND (g.POST_DAT > '19990601')

Query #3
select distinct(a.id_num)
from v_aldr a,v_gift g
where a.id_num = g.id_num
and (g.devl_org_unit_cod IN ('243','225','230','250','235','240','255','265','3105100','245','270'))
AND (g.FIN_DSGN_COD IN ('G', 'P', 'MG', 'MP'))
AND (g.POST_DAT > '20010531')

Query #4
SELECT DISTINCT(id_NUM)
FROM V_LIST
WHERE mbrshp_lst_cod IN ('TRA','TRR','TRC','TRK','TRO','TRX','TR1','TR2','PRC','DCM','LCP')

Any ideas? I was thinking I could use INTERSECT and MINUS/EXCLUDE but we are on SQL Server 2000 and they don't seem to be supported. Maybe I can use EXISTS/NOT EXISTS?

Thanks.

 

[maswien]

try this:

select distinct(a.id_num)
from v_aldr a,v_gift g
where ( 
a.id_num = g.id_num
AND (a.org_typ_1_cod  = '1') and (a.acct_stat_cod IN ('0','1')) and (g.fin_dsgn_cod IN ('G','P','MG','MP'))
and (g.post_dat BETWEEN '19400101' AND '20010531') 
and (g.devl_org_unit_cod LIKE '243%'
OR g.devl_org_unit_cod LIKE '225%'
OR g.devl_org_unit_cod LIKE '230%'
OR g.devl_org_unit_cod LIKE '250%'
OR g.devl_org_unit_cod LIKE '235%'
OR g.devl_org_unit_cod LIKE '240%'
OR g.devl_org_unit_cod LIKE '255%'
OR g.devl_org_unit_cod LIKE '265%'
OR g.devl_org_unit_cod = '3105100'
OR g.devl_org_unit_cod LIKE '245%'
) and
(
 a.id_num = g.id_num
and  (g.ACCT_NUM = '440301') 
AND (g.FIN_DSGN_COD IN ('G', 'P', 'MG', 'MP')) 
AND (g.POST_DAT > '19990601')
) and
a.id_num not in 
(SELECT DISTINCT(id_NUM)
FROM V_LIST 
WHERE mbrshp_lst_cod IN ('TRA','TRR','TRC','TRK','TRO','TRX','TR1','TR2','PRC','DCM','LCP')
) and
(
g.devl_org_unit_cod Not IN ('243','225','230','250','235','240','255','265','3105100','245','270')
or g.FIN_DSGN_COD  Not IN ('G', 'P', 'MG', 'MP')
or g.POST_DAT <= '20010531'
)

 

[rmelnyck]

Thanks, I tried it but i am only getting one record instead of around 1000.

 

[maswien]

the code combine the 4 queries into 1, can you check following results?

select count(distinct(a.id_num))
from v_aldr a,v_gift g
where ( 
a.id_num = g.id_num
AND (a.org_typ_1_cod  = '1') and (a.acct_stat_cod IN ('0','1')) and (g.fin_dsgn_cod IN ('G','P','MG','MP'))
and (g.post_dat BETWEEN '19400101' AND '20010531') 
and (g.devl_org_unit_cod LIKE '243%'
OR g.devl_org_unit_cod LIKE '225%'
OR g.devl_org_unit_cod LIKE '230%'
OR g.devl_org_unit_cod LIKE '250%'
OR g.devl_org_unit_cod LIKE '235%'
OR g.devl_org_unit_cod LIKE '240%'
OR g.devl_org_unit_cod LIKE '255%'
OR g.devl_org_unit_cod LIKE '265%'
OR g.devl_org_unit_cod = '3105100'
OR g.devl_org_unit_cod LIKE '245%'
) and
(
 a.id_num = g.id_num
and  (g.ACCT_NUM = '440301') 
AND (g.FIN_DSGN_COD IN ('G', 'P', 'MG', 'MP')) 
AND (g.POST_DAT > '19990601')
)

SELECT count(DISTINCT(id_NUM))
FROM V_LIST 
WHERE mbrshp_lst_cod IN ('TRA','TRR','TRC','TRK','TRO','TRX','TR1','TR2','PRC','DCM','LCP')

SELECT count(DISTINCT(id_NUM))
FROM V_LIST 
WHERE mbrshp_lst_cod IN ('TRA','TRR','TRC','TRK','TRO','TRX','TR1','TR2','PRC','DCM','LCP')

 

[maswien]

sorry the second is :

select count(distinct(a.id_num))
from v_aldr a,v_gift g
where a.id_num = g.id_num
and  (g.devl_org_unit_cod IN ('243','225','230','250','235','240','255','265','3105100','245','270')) 
AND (g.FIN_DSGN_COD  IN ('G', 'P', 'MG', 'MP')) 
AND (g.POST_DAT > '20010531')