'union' 2 different tables together

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 2005 Forums

Transact-SQL (2005)

'union' 2 different tables together

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-06 : 09:12:52

Hi,

I have 2 tables I would like to bring back as 1 resultSet. Both queries return different columsn back.

For example

query1:
select top 10 counterID, userID, caption, photoDate from tblextraphotos

query2:
select top 10 userID,friendID,dateAdded from tblfriends

I want to bring back the top 10 records for each, and I want to order the combined results by date (photoDate,dateAdded)

How can I do this ? It appears that UNION isn't the tool for this job ?

I realize I will have a bunch of extra columns with NULL values, well thats what I am expecting it to look like anyways.

Any help much appreciated !

Thanks again,
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 09:27:30

[code]SELECT r.IDVal1,r.IDVal2,r.caption,r.DateVal
FROM
(
select counterID AS IDVal1, userID AS IDVal2, caption, photoDate AS DateVal
from
(
select top 10 counterID, userID, caption, photoDate from tblextraphotos
order by photoDate
)t

union all

select userID,friendID,NULL,dateAdded
from
(
select top 10 userID,friendID,dateAdded from tblfriends
order by dateAdded)t2
)r
order by r.DateValue[/code]
since you dont have corresponding column for caption i'm placing NULL for that.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-06 : 09:33:50

Hi Visakh16,

I was hoping to have a bunch of NULL columsn, where the column names did not align. So the sample data returned would look something like this.

Thanks again!
Mike123

counterID/userID/caption/photoDate / userID/FriendID/dateAdded

NULL / NULL / NULL / NULL / 100 / 200 / 2000-10-10
NULL / NULL / NULL / NULL / 200 / 200 / 2000-10-09
NULL / NULL / NULL / NULL / 300 / 200 / 2000-10-08
100 / 456 / caption1 / 2000-10-10 / 100 / 200 / 2000-10-07
101 / 458 / caption2 / 2000-10-10 / 100 / 200 / 2000-10-06
102 / 459 / caption3 / 2000-10-10 / 100 / 200 / 2000-10-05

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 09:42:28

you could have provided this before.

select t1.counterID, t1.userID, t1.caption, t1.photoDate, 
t2.userID,t2.friendID,t2.dateAdded 
from
(
select top 10 counterID, userID, caption, photoDate from tblextraphotos 
order by photoDate)t1
full outer join
(
select top 10 userID,friendID,dateAdded from tblfriends
order by dateAdded
)t2
on t1.userID=t2.userID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 09:54:18

Mike, you took the harder route?
This is a continuation from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111760 ?
Especially post made 10/02/2008 : 09:22:24?

SELECT TOP 10	counterID,
		userID,
		caption,
		CAST(NULL AS INT) AS friendID,
		photoDate
FROM		tblExtraPhotos 

UNION ALL

SELECT TOP 10	NULL,
		userID,
		NULL,
		friendID,
		dateAdded
FROM		tblfriends

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-06 : 10:16:40

Hey Peso,

Thanks for chimin in! You are correct, this is a continuation from that thread. To avoid changing direction of that thread I just thought it would be better to post something seperate here.

I have not decided on which way to advance with my query. I am writing out all the pros / cons of each method, with alot of details.

I am seeing a few small issues on the trigger/ logtable method, mostly related to data formatting, so I want to keep looking at both options. I will post the issues in more details once I have all the details I can possibly think of.

thanks again!
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources