Join two queries with UNION ALL. Please advise!

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)

Join two queries with UNION ALL. Please advise!

Author

Topic

panthagani
Yak Posting Veteran

58 Posts

Posted - 2009-07-21 : 09:21:10

Hi,

I need to show the results of each of these queries as a single result set. I tried with UNION ALL but only the result set of the first query is shown. Please advise.

select B.tname,count(A1.aglobalcaseid) as resolved from tblcase A1 INNER JOIN tbluser B
on A1.nOwnerID = B.aUserID
where A1.dlastresolvedAT between getdate() and getdate()-1 and
A1.nsubcasetype is null and A1.bdeleted=0 group by B.tname

UNION ALL

select C.tname as User_Name, count(distinct A2.aglobalcaseid) as Cr_intCount from tblcase A2 INNER JOIN tblcasecr B
on A2.aGlobalCaseID = B.nCaseID
INNER JOIN tbluser C on A2.nOwnerID = C.aUserID
INNER JOIN tblcrtitle D on B.nCRID = D.nCRID
where A2.dlastresolvedAT between getdate() and getdate()-1
and A2.nqueueid=5 group by C.tname

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 09:26:35

try to execute the 2nd query by itself. Any result return ?

KH
[spoiler]Time is always against us[/spoiler]

panthagani
Yak Posting Veteran

58 Posts

Posted - 2009-07-21 : 09:32:20

Thanks KH. No rows returned. Just the column headings:

User_name Cr_IntCount

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 09:41:15

why are you using "between getdate() and getdate() - 1" ?

Did you try to run this ?


select getdate(), getdate() - 1

I wonder how did you get any result from your 1st query

KH
[spoiler]Time is always against us[/spoiler]

panthagani
Yak Posting Veteran

58 Posts

Posted - 2009-07-21 : 09:56:06

OK. I changed the query a little bit... Please advise.

select B.tname,count(A1.aglobalcaseid) as resolved from tblcase A1 INNER JOIN tbluser B
on A1.nOwnerID = B.aUserID
where A1.dlastresolvedAT <= getdate() and A1.dlastresolvedAT > getdate()-1 and
A1.nsubcasetype is null and A1.bdeleted=0 group by B.tname

UNION ALL

select C.tname as User_Name, count(distinct A2.aglobalcaseid) as Cr_intCount from tblcase A2 INNER JOIN tblcasecr B
on A2.aGlobalCaseID = B.nCaseID
INNER JOIN tbluser C on A2.nOwnerID = C.aUserID
INNER JOIN tblcrtitle D on B.nCRID = D.nCRID
where A2.dlastresolvedAT <= getdate() and A2.dlastresolvedAT > getdate()-1
and A2.nqueueid=5 group by C.tname

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 10:05:15

so does it work for you ?

KH
[spoiler]Time is always against us[/spoiler]

panthagani
Yak Posting Veteran

58 Posts

Posted - 2009-07-21 : 11:01:36

Unfortunately, it does not...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 11:12:13

any error or anything ?

if not check your query JOIN and WHERE condition make sure it is correct.

or post your table structure with some sample data and the required result

see http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources