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.
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 TABLE123456 Test Desc Test T3123456 Closed Desc Closed T1Example Union Query:/* T1 */select distinct A.ID as ACCOUNT, B.NAME as BRNAME, BG.NAME as BGNAME, C.NAMEFROM 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.IDINNER JOIN BILLD BD on B.BILLID = BD.BILLIDINNER JOIN CALENDAR C on B.CALID = C.CALIDWHERE B.TFLAG = 1and 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 nullor 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.NAMEFROM BILL BINNER JOIN BILLG BG on BG.ID = B.IDINNER JOIN BILLD BD on B.BILLID = BD.BILLID INNER JOIN ADMIN A on A.ADMID = BD.ROWIDINNER JOIN CALENDAR C on B.CALID = C.CALIDWHERE 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 nullor 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.NAMEFROM BILL BINNER JOIN BILLG BG on BG.ID = B.IDINNER JOIN BILLD BD on B.BILLID = BD.BILLIDINNER JOIN ADMIN P on P.ADMINID = BD.ROWIDINNER 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.CALIDWHERE 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 nullor 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.NAMEFROM 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.IDINNER JOIN BILLD BD on B.BILLID = BD.BILLIDINNER JOIN CALENDAR C on B.CALID = C.CALIDWHERE B.TFLAG = 1and 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 nullor 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.NAMEFROM BILL BINNER JOIN BILLG BG on BG.ID = B.IDINNER JOIN BILLD BD on B.BILLID = BD.BILLID INNER JOIN ADMIN A on A.ADMID = BD.ROWIDINNER JOIN CALENDAR C on B.CALID = C.CALIDWHERE 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 nullor 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.NAMEFROM BILL BINNER JOIN BILLG BG on BG.ID = B.IDINNER JOIN BILLD BD on B.BILLID = BD.BILLIDINNER JOIN ADMIN P on P.ADMINID = BD.ROWIDINNER 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.CALIDWHERE 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 nullor A.DATE > GETDATE ())and YEAR(A.EFFDATE) <> '2099'and YEAR(A.EFFDATE) <> '9999')t)rWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|