Merging Resultsets

Question

Post reply

Merging Resultsets

Vishal Singh

SSCertifiable

Points: 5830
More actions
May 24, 2010 at 7:03 am

#103945

What I am looking for is can I get a single combined result which will match to output of these two queries. I have tried UNION ALL but looking out for something more suitable to performance or something more simpler than this.
create table t1 (catid int, orgid varchar(10))
create table t2 (catid int)
insert into t1 values (1,'PETER')
insert into t1 values (2,'a01')
insert into t1 values (3,'*')
insert into t1 values (4,'a01')
insert into t1 values (5,'PETER')
insert into t1 values (6,'a02')
insert into t1 values (7,'a03')
insert into t2 values (1)
insert into t2 values (2)
insert into t2 values (3)
insert into t2 values (4)
insert into t2 values (5)
insert into t2 values (6)
insert into t2 values (7)
--Query 1
SELECT T2.CATID FROM T2 INNER JOIN T1 ON T1.CATID = T2.CATID
WHERE T1.ORGID IN ('*','A01','PETER')
--Query 2
SELECT '' FROM T2 INNER JOIN T1 ON T1.CATID = T2.CATID
WHERE T1.ORGID NOT IN ('*','A01','PETER')
-Forum Etiquette: How to post Performance Problems[/url]
-Forum Etiquette: How to post data/code to get the best help [/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I'm sorry, but I can't understand why UNION ALL doesnt' fit your needs.

Can you post the desired output of your "merged" query please?

-- Gianluca Sartori

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

DROP table #t1

DROP table #t2

create table #t1 (catid int, orgid varchar(10))

create table #t2 (catid int)

insert into #t1 values (1,'PETER')

insert into #t1 values (2,'a01')

insert into #t1 values (3,'*')

insert into #t1 values (4,'a01')

insert into #t1 values (5,'PETER')

insert into #t1 values (6,'a02')

insert into #t1 values (7,'a03')

insert into #t1 values (8,NULL) -- <--- extra sample row

insert into #t2 values (1)

insert into #t2 values (2)

insert into #t2 values (3)

insert into #t2 values (4)

insert into #t2 values (5)

insert into #t2 values (6)

insert into #t2 values (7)

insert into #t2 values (8)

-- Result should be Query 1 UNION ALL Query 2

--Query 1

SELECT T2.CATID

FROM #T2 t2

INNER JOIN #T1 t1 ON T1.CATID = T2.CATID

WHERE T1.ORGID IN ('*','A01','PETER')

--Query 2

SELECT ''

FROM #T2 t2

INNER JOIN #T1 t1 ON T1.CATID = T2.CATID

WHERE T1.ORGID NOT IN ('*','A01','PETER')

-- Answer 1 will NOT work if T1.ORGID is nullable

SELECT t1.CATID

FROM #T2 t2

LEFT JOIN #T1 t1 ON T1.CATID = T2.CATID AND T1.ORGID IN ('*','A01','PETER')

-- Answer 2 will work if T1.ORGID is nullable

SELECT CASE WHEN T1.ORGID IN ('*','A01','PETER') THEN t1.CATID END

FROM #T2 t2

INNER JOIN #T1 t1 ON T1.CATID = T2.CATID

WHERE T1.ORGID IS NOT NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 3

Chris Morris-439714 (5/24/2010)
DROP table #t1 DROP table #t2 create table #t1 (catid int, orgid varchar(10)) create table #t2 (catid int) insert into #t1 values (1,'PETER') insert into #t1 values (2,'a01') insert into #t1 values (3,'*') insert into #t1 values (4,'a01') insert into #t1 values (5,'PETER') insert into #t1 values (6,'a02') insert into #t1 values (7,'a03') insert into #t1 values (8,NULL) -- <--- extra sample row insert into #t2 values (1) insert into #t2 values (2) insert into #t2 values (3) insert into #t2 values (4) insert into #t2 values (5) insert into #t2 values (6) insert into #t2 values (7) insert into #t2 values (8) -- Result should be Query 1 UNION ALL Query 2 --Query 1 SELECT T2.CATID FROM #T2 t2 INNER JOIN #T1 t1 ON T1.CATID = T2.CATID WHERE T1.ORGID IN ('*','A01','PETER') --Query 2 SELECT '' FROM #T2 t2 INNER JOIN #T1 t1 ON T1.CATID = T2.CATID WHERE T1.ORGID NOT IN ('*','A01','PETER') -- Answer 1 will NOT work if T1.ORGID is nullable SELECT t1.CATID FROM #T2 t2 LEFT JOIN #T1 t1 ON T1.CATID = T2.CATID AND T1.ORGID IN ('*','A01','PETER') -- Answer 2 will work if T1.ORGID is nullable SELECT CASE WHEN T1.ORGID IN ('*','A01','PETER') THEN t1.CATID END FROM #T2 t2 INNER JOIN #T1 t1 ON T1.CATID = T2.CATID WHERE T1.ORGID IS NOT NULL

This worked very well. Thanks for the help.

to answer the previous reply, UNION ALL is working perfectly but I was looking out for some other alternatives.

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]