SQL query using INTERSECT or EXISTS? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL query using INTERSECT or EXISTS?

Author

Topic

rmelnyck
Starting Member

4 Posts

Posted - 2005-12-01 : 10:06:54

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

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 10:33:50

INNER JOIN the first two

then OUTER JOIN the second two, and also put a WHERE clause where the target field of the JOIN IS NULL


SELECT *
FROM
(
   SELECT ... Query #1
) Q1
JOIN
(
   SELECT ... Query #2
) Q2
  ON Q2.id_num = Q1.id_num
LEFT OUTER JOIN
(
   SELECT ... Query #3
) Q3
  ON Q3.id_num = Q1.id_num
LEFT OUTER JOIN
(
   SELECT ... Query #4
) Q4
  ON Q4.id_NUM = Q1.id_num
WHERE     Q3.id_num IS NULL
      AND Q4.id_NUM IS NULL

Kristen

rmelnyck
Starting Member

4 Posts

Posted - 2005-12-01 : 12:47:15

Thanks Kristen!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources