merge 2 queries data - SQL Server Forums

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)

merge 2 queries data

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-29 : 07:12:36

hi,

i have to add 2 queries data into one select command and display it. i can not use union bcz the no of fields are different is there any way to merge to result into one.
here are queries.

select max(upload_date),caclientsloginid
from fileinfo
where right(rtrim(filename),3)= 'zip'
group by caclientsloginid

select max(upload_date) as 'Processed Send',caclientsloginid as 'Client ID',noofmonths as 'No. Of Months' from backpdfinfo group
by caclientsloginid,noofmonths order by caclientsloginid

thx

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-29 : 07:40:59

You can use UNION, simply add in a placeholder column where you don't have one:


select max(upload_date) AS Processed, caclientsloginid As Client, 0 AS NoOfMonths
from fileinfo
where right(rtrim(filename),3)= 'zip'
group by caclientsloginid

UNION ALL

select max(upload_date),caclientsloginid,noofmonths from backpdfinfo group
by caclientsloginid,noofmonths order by caclientsloginid

imughal
Posting Yak Master

192 Posts

Posted - 2004-06-30 : 00:20:27

hi thx but little change in result required.

i want to add just a colum result in 1st qry from second qry

the result of 1st qry is [total rec 25]

startdate clientid

the result of 2st qry is [total rec 26]

Enddate clientid noof months

and i want result like that

startdate enddate client id noof months

is there any posibility to do that.

thx

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-30 : 02:35:02

In that case, you should use a JOIN with derived tables instead of UNION. An simple example of how this can be done:


SELECT ClientID, StartDate, EndDate FROM
 (SELECT ClientID, StartDate FROM TableA) A
INNER JOIN 
 (SELECT ClientID, EndDate FROM TableB) B
ON A.ClientID = B.ClientID

Derived tables basically let you use the output of a query in place of an actual table, and use it like a real table, simply by adding an alias to the query.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources