SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple Union with Not Exist Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kaneda0149
Starting Member

USA
1 Posts

Posted - 06/19/2013 :  16:41:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1643 Posts

Posted - 06/19/2013 :  18:05:01  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/20/2013 :  01:00:54  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000