Multiple Union with Not Exist Query

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

General SQL Server Forums

New to SQL Server Programming

Multiple Union with Not Exist Query

Author

Topic

kaneda0149
Starting Member

1 Post

Posted - 2013-06-19 : 16:41:46

I am pulling 3 queries with a union statement which works perfectly for 90% of the data extracted but the problem I am running into is when the record existing in 2 or all 3 unions; I only want data from T2 and T3 where it does not exist in T1.

The information is accurate where they do reside in both or all 3 but since the data I do want is being returned in T1, I want T2 and T3 to exclude it when it runs. The key is the ID where 123456 exist in T1 and T3, don't return results in T3. This would be the same if it exist in T1, don't return results in T2 or T3.

Hope this makes sense, any help is greatly appreciated.

Example Output of Dup Instance:
ID BRNAME BGNAME TABLE
123456 Test Desc Test T3
123456 Closed Desc Closed T1

Example Union Query:

/* T1 */
select distinct 
A.ID as ACCOUNT
, B.NAME  as BRNAME
, BG.NAME as BGNAME
, C.NAME
FROM ADMIN A 
INNER JOIN BILL B on A.IDSET = B.IDSET and B.EFF <= GETDATE() and (B.CANCEL >= GETDATE() or B.CANCEL is null)
INNER JOIN BILLG BG on BG.ID = B.ID
INNER JOIN BILLD BD on B.BILLID = BD.BILLID
INNER JOIN CALENDAR C on B.CALID = C.CALID
WHERE B.TFLAG = 1
and BG.NAME not like 'Example'
and BG.NAME not like 'Test'
and A.ID not like 'zz%'
and A.ID not like 'xx%'
and (A.DATE is null
or A.DATE > GETDATE ())
and YEAR(A.EFFDATE)  <> '2099'
and YEAR(A.EFFDATE)  <> '9999'

UNION

/* T2 */
select distinct 
A.ID as ACCOUNT
, B.NAME  as BRNAME
, BG.NAME as BGNAME
, C.NAME
FROM BILL B
INNER JOIN BILLG BG on BG.ID = B.ID
INNER JOIN BILLD BD on B.BILLID = BD.BILLID 
INNER JOIN ADMIN A on A.ADMID = BD.ROWID
INNER JOIN CALENDAR C on B.CALID = C.CALID
WHERE 
B.TFLAG = 0 
and BD.TYPEID is null 
and BG.NAME not like 'Example'
and BG.NAME not like 'Test'
and A.ID not like 'zz%'
and A.ID not like 'xx%'
and (A.DATE is null
or A.DATE > GETDATE ())
and YEAR(A.EFFDATE)  <> '2099'
and YEAR(A.EFFDATE)  <> '9999'

UNION

/* T3 */
select distinct 
A.ID as ACCOUNT
, B.NAME  as BRNAME
, BG.NAME as BGNAME
, C.NAME
FROM BILL B
INNER JOIN BILLG BG on BG.ID = B.ID
INNER JOIN BILLD BD on B.BILLID = BD.BILLID
INNER JOIN ADMIN P on P.ADMINID = BD.ROWID
INNER JOIN ADMINR AR on AR.PID = P.ADMINID and AR.TYPE = BD.TYPE and (coalesce(AR.EFFDATE, getdate()) <= GETDATE()) and (coalesce(AR.DATE, getdate()) >= GETDATE())
INNER JOIN ADMIN A on A.IDSET = AR.IDSET and A.EFFDATE <= GETDATE() and ISNULL(A.DATE, GETDATE()) >= GETDATE()
INNER JOIN CALENDAR C on B.CALID = C.CALID
WHERE 
B.TFLAG = 0 
and BG.NAME not like 'Example'
and BG.NAME not like 'Test'
and A.ID not like 'zz%'
and A.ID not like 'xx%'
and (A.DATE is null
or A.DATE > GETDATE ())
and YEAR(A.EFFDATE)  <> '2099'
and YEAR(A.EFFDATE)  <> '9999'

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-06-19 : 18:05:01

If the data from each UNION subset is the same, you could use UNION ALL to connect them. This would insure that duplicates are removed.

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 01:00:54

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ACCOUNT ORDER BY Cat) AS Seq,*
FROM
(
select 1 AS Cat,
A.ID as ACCOUNT
, B.NAME as BRNAME
, BG.NAME as BGNAME
, C.NAME
FROM ADMIN A
INNER JOIN BILL B on A.IDSET = B.IDSET and B.EFF <= GETDATE() and (B.CANCEL >= GETDATE() or B.CANCEL is null)
INNER JOIN BILLG BG on BG.ID = B.ID
INNER JOIN BILLD BD on B.BILLID = BD.BILLID
INNER JOIN CALENDAR C on B.CALID = C.CALID
WHERE B.TFLAG = 1
and BG.NAME not like 'Example'
and BG.NAME not like 'Test'
and A.ID not like 'zz%'
and A.ID not like 'xx%'
and (A.DATE is null
or A.DATE > GETDATE ())
and YEAR(A.EFFDATE) <> '2099'
and YEAR(A.EFFDATE) <> '9999'

UNION

/* T2 */
select 2 AS cat,
A.ID as ACCOUNT
, B.NAME as BRNAME
, BG.NAME as BGNAME
, C.NAME
FROM BILL B
INNER JOIN BILLG BG on BG.ID = B.ID
INNER JOIN BILLD BD on B.BILLID = BD.BILLID
INNER JOIN ADMIN A on A.ADMID = BD.ROWID
INNER JOIN CALENDAR C on B.CALID = C.CALID
WHERE
B.TFLAG = 0
and BD.TYPEID is null
and BG.NAME not like 'Example'
and BG.NAME not like 'Test'
and A.ID not like 'zz%'
and A.ID not like 'xx%'
and (A.DATE is null
or A.DATE > GETDATE ())
and YEAR(A.EFFDATE) <> '2099'
and YEAR(A.EFFDATE) <> '9999'

UNION

/* T3 */
select 3,
A.ID as ACCOUNT
, B.NAME as BRNAME
, BG.NAME as BGNAME
, C.NAME
FROM BILL B
INNER JOIN BILLG BG on BG.ID = B.ID
INNER JOIN BILLD BD on B.BILLID = BD.BILLID
INNER JOIN ADMIN P on P.ADMINID = BD.ROWID
INNER JOIN ADMINR AR on AR.PID = P.ADMINID and AR.TYPE = BD.TYPE and (coalesce(AR.EFFDATE, getdate()) <= GETDATE()) and (coalesce(AR.DATE, getdate()) >= GETDATE())
INNER JOIN ADMIN A on A.IDSET = AR.IDSET and A.EFFDATE <= GETDATE() and ISNULL(A.DATE, GETDATE()) >= GETDATE()
INNER JOIN CALENDAR C on B.CALID = C.CALID
WHERE
B.TFLAG = 0
and BG.NAME not like 'Example'
and BG.NAME not like 'Test'
and A.ID not like 'zz%'
and A.ID not like 'xx%'
and (A.DATE is null
or A.DATE > GETDATE ())
and YEAR(A.EFFDATE) <> '2099'
and YEAR(A.EFFDATE) <> '9999'
)t
)r
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources