unioning multiple queries together (dif columns)

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)

unioning multiple queries together (dif columns)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-07-03 : 16:19:24

Hi,

I am trying to figure out if this is possible. What I am trying to do is bring back in descending order a combination of a few queries. (The queries are all hitting different tables.)

There are 5 queries, and all 5 have the same base 3 columns. However there are an additional 3 columns on 2 of the queries, and a further additional 2 columns on 2 of them. Basically the data structures are different.

I want to bring back a list of these queries ordered by the "eventDate", which is a column they all have.

Whats the best way to handle something like this? Any advice much appreciated. I can also post table structures etc if that helps. I think this is just a more general question tho.

Thanks very much,
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 16:27:18

Yes, it would help with sample data and expected output.

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-07-03 : 17:33:03

Hey Peso,

There is quite a bit of sample data and structures, and its fairly confusing for me at least. I'll do my best, please let me know if it doesnt make sense.

I'm wondering how would you bring all these records back as 1 result set? Can you union queries onto other queries with mismatching columns? Effectively UNIONING onto a NULL column? Is this a bad strategy?

In plain english. -> This is for a web application that I want to display the "latest events" happening. These events are drawn from 5 different tables, and ordered by the "EventDate" DESC. I'm not sure if I should look at starting a log table, or do a query like this. I think it would be much better idea if I could write an efficient query, especially since I plan to take this further and write user specific queries after. Any opinions?

I've left out the desired results for this post as I'm just trying to make sure I have all the facts straight for you, and I'm taking the correct approach first.

Thanks once again, much appreciated
Mike123

Table 1 (rows brought back)
UserID,NameOnline,EventDate,friendID,friend_nameOnline

SELECT TOP 100 Ud.UserID, UD.NameOnline, EventDate = F.dateAdded, friendID, UD2.nameOnline as friend_NameOnline, TypeId = 1
from tblFriends F
JOIN tblUserDetails UD ON F.userID = UD.UserID
JOIN tblUserDetails UD2 ON F.FriendID = UD2.UserID
ORDER BY eventDate DESC

Table 2 (rows brought back)
userID,nameOnline,EventDate,hotUserID,friend_nameOnline

SELECT TOP 100 Ud.UserID, UD.NameOnline, EventDate = H.dateAdded, hotUserID, UD2.nameOnline as friend_NameOnline, TypeId = 2
from tblHotList H
JOIN tblUserDetails UD ON H.userID = UD.UserID
JOIN tblUserDetails UD2 ON H.hotUserID = UD2.UserID
ORDER BY eventDate DESC

Table 3 (rows brought back)userID,nameOnline,EventDate,counterID,photoID

SELECT TOP 100 Ud.UserID, UD.NameOnline, EventDate = P.photoDate , TypeId = 3, counterID, photoID
from tblExtraPhotos P
JOIN tblUserDetails UD ON P.userID = UD.UserID
Where
status = 1 and ratingID =1
ORDER BY eventDate DESC

Table 4 (rows brought back)
userID,nameOnline,EventDate

SELECT TOP 100 userID, nameOnline, EventDate = lastUpdated, TypeId = 4
from tblUserDetails UD
Where
active = 1 or active = 4
ORDER BY eventDate DESC

Table 5 (rows brought back)
userID,nameOnline,EventDate

SELECT TOP 100 userID, nameOnline, EventDate = date, TypeId = 5
from tblUserDetails UD
Where
active = 1 or active = 4
ORDER BY eventDate DESC

Create Tables

CREATE TABLE [dbo].[tblFriends](
[UserID] [int] NOT NULL,
[FriendID] [int] NOT NULL,
[dateAdded] [smalldatetime] NULL)

CREATE TABLE [dbo].[tblHotList](
[UserID] [int] NOT NULL,
[hotUserID] [int] NOT NULL,
[dateAdded] [smalldatetime] NULL)

CREATE TABLE [dbo].[tblExtraPhotos](
[counterID] [int] IDENTITY(1,1) NOT NULL,
[photoID] [tinyint] NOT NULL,
[userID] [int] NOT NULL,
[photoDate] [smalldatetime] NOT NULL )

CREATE TABLE [dbo].[tblUserDetails](
[UserID] [int] NOT NULL,
[NameOnline] [varchar](15) NULL,
[LastLoggedIn] [datetime] NULL,
[LastUpdated] [smalldatetime] NULL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-03 : 17:44:48

Yes you can union result sets together even if the columns are mismatched. For this problem, you don't need to show us your current queries or explain the situation. Sometimes this is needed, but your real question here is how to union together your result sets. You just need to show us example rows (INSERT INTO statements) from each of your queries and the expected result set when the queries are unioned together. We then come up with a solution for you, which you then would modify to include the queries that you currently have.

When posting questions, make the problem as easy as possible regardless of how complex the situation really is. Usually we can come up with a solution pretty quickly if it is explained simply and clearly. You just might need to tweak it to fit your exact needs. If our solution gives you the expected output using your sample data but doesn't work when you run it in your environment, you then come up with sample data that shows the new issue.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-07-04 : 10:38:29

Wow, my power died on this first post while checking it in preview mode. I guess I have no choice but to type it all up again :).. Here we go!

I have made a few changes from my original post on the data structures in order to simplify things, so please ignore my first post.

What I need to do is get the results of these 4 queries, and transform them into 1 result set. Bringing back the top 100 is sufficient. This one result set should be sorted by "ORDER BY evendDate DESC"

There are quite alot of rows in these tables, so I think limiting the rows brought back to rows with an eventDate in the past 24 hours should help performance? This is also ok to do, altho not necessary.
I am just suggesting it to speed performance.

I hope this data below is useful and clear. If theres anything I can do to make it better please let me know. Your help is much appreciated!!

Thanks once again :)
mike123

--QUERIES

--QUERY #1
--Rows: UserID,NameOnline,EventDate,friendID,friend_nameOnline,typeID

SELECT TOP 100 Ud.UserID, UD.NameOnline, EventDate = F.dateAdded, friendID, UD2.nameOnline as friend_NameOnline, TypeId = 1
from tblFriends F
JOIN tblUserDetails UD ON F.userID = UD.UserID
JOIN tblUserDetails UD2 ON F.FriendID = UD2.UserID
ORDER BY eventDate DESC

--QUERY #2 - (omitted)

--QUERY #3
Rows: userID,nameOnline,EventDate,counterID,photoID,typeID

SELECT TOP 100 Ud.UserID, UD.NameOnline, EventDate = P.photoDate , TypeId = 3, counterID, photoID
from tblExtraPhotos P
JOIN tblUserDetails UD ON P.userID = UD.UserID
--Where
--status = 1 and ratingID =1
ORDER BY eventDate DESC

--QUERY #4
Rows: userID,nameOnline,EventDate,typeID

SELECT TOP 100 userID, nameOnline, EventDate = lastUpdated, TypeId = 4
from tblUserDetails UD
--Where
--active = 1 or active = 4
ORDER BY eventDate DESC

--QUERY #5
Rows: userID,nameOnline,EventDate,typeID

SELECT TOP 100 userID, nameOnline, EventDate = date, TypeId = 5
from tblUserDetails UD
--Where
--active = 1 or active = 4
ORDER BY eventDate DESC

--CREATE TABLE STATEMENTS WITH DDL AND DML

--BEGIN tblFriends

CREATE TABLE [dbo].[tblFriends](
[UserID] [int] NOT NULL,
[FriendID] [int] NOT NULL,
[dateAdded] [smalldatetime] NULL)

INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,101,DATEADD(mi,-1,getDate()))
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,105,DATEADD(mi,-1,getDate()))
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,110,DATEADD(mi,-1,getDate()))
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,115,DATEADD(mi,-1,getDate()))
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,120,DATEADD(mi,-1,getDate()))
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,122,DATEADD(mi,-1,getDate()))
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (102,100,DATEADD(mi,-1,getDate()))

--END tblFriends

--BEGIN tblExtraPhotos

CREATE TABLE [dbo].[tblExtraPhotos](
[counterID] [int] IDENTITY(1,1) NOT NULL,
[photoID] [tinyint] NOT NULL,
[userID] [int] NOT NULL,
[photoDate] [smalldatetime] NOT NULL )

INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (1,101,DATEADD(mi,-1,getDate()))
INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (1,102,DATEADD(mi,-1,getDate()))
INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (1,103,DATEADD(mi,-1,getDate()))
INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (1,105,DATEADD(mi,-1,getDate()))
INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (2,105,DATEADD(mi,-1,getDate()))

--END tblExtraPhotos

--BEGIN tblUserDetails

CREATE TABLE [dbo].[tblUserDetails](
[UserID] [int] NOT NULL,
[NameOnline] [varchar](15) NULL,
[date] [datetime] NULL,
[LastUpdated] [smalldatetime] NULL)

INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (100,billy,DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (101,bob,DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (105,jack,DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (110,sarah,DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (115,mike,DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (120,kim,DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (122,rob,DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (102,tony,DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (103,rory,DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))

--END tblUserDetails

Desired Results Brought back (this is just the top 6, I think its adequate)

userID,nameOnline,eventDate,friendID,friend_nameOnline,typeID,counterID,photoID

100 ,billy ,1 min ago,101 ,bob ,1 ,NULL ,NULL

101 ,bob ,2 min ago,NULL ,NULL ,3 ,IDENTITY ,1

100 ,billy ,3 min ago,NULL ,NULL ,4 ,NULL ,NULL

101 ,bob ,4 min ago,NULL ,NULL ,5 ,NULL ,NULL

100 ,billy ,5 min ago,105 ,jack ,1 ,NULL ,NULL

102 ,tony ,6 min ago,NULL ,NULL ,3 ,IDENTITY ,1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-05 : 12:59:02

I am getting errors when I run your insert statements.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-07-05 : 13:44:10

Hi Tara,

My apologies, I'm not sure how I managed to strip the quotes around the names.

This below is double checked and verified working. Thanks again :)

Mike123

--CREATE TABLE STATEMENTS WITH DDL AND DML

--BEGIN tblFriends

CREATE TABLE [dbo].[tblFriends](
[UserID] [int] NOT NULL,
[FriendID] [int] NOT NULL,
[dateAdded] [smalldatetime] NULL)

INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,101,DATEADD(mi,-1,getDate()))
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,105,DATEADD(mi,-1,getDate()))
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,110,DATEADD(mi,-1,getDate()))
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,115,DATEADD(mi,-1,getDate()))
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,120,DATEADD(mi,-1,getDate()))
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (100,122,DATEADD(mi,-1,getDate()))
INSERT INTO tblFriends (userID,friendID,dateAdded) VALUES (102,100,DATEADD(mi,-1,getDate()))

--END tblFriends

--BEGIN tblExtraPhotos

CREATE TABLE [dbo].[tblExtraPhotos](
[counterID] [int] IDENTITY(1,1) NOT NULL,
[photoID] [tinyint] NOT NULL,
[userID] [int] NOT NULL,
[photoDate] [smalldatetime] NOT NULL )

INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (1,101,DATEADD(mi,-1,getDate()))
INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (1,102,DATEADD(mi,-1,getDate()))
INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (1,103,DATEADD(mi,-1,getDate()))
INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (1,105,DATEADD(mi,-1,getDate()))
INSERT INTO tblExtraPhotos (photoID,userID,photoDate) VALUES (2,105,DATEADD(mi,-1,getDate()))

--END tblExtraPhotos

--BEGIN tblUserDetails

CREATE TABLE [dbo].[tblUserDetails](
[UserID] [int] NOT NULL,
[NameOnline] [varchar](15) NULL,
[date] [datetime] NULL,
[LastUpdated] [smalldatetime] NULL)

INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (100,'billy',DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (101,'bob',DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (105,'jack',DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (110,'sarah',DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (115,'mike',DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (120,'kim',DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (122,'rob',DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (102,'tony',DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))
INSERT INTO tblUserDetails (userID,nameOnline,date,lastUpdated) VALUES (103,'rory',DATEADD(mi,-1,getDate()),DATEADD(mi,-1,getDate()))

--END tblUserDetails

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-05 : 14:05:00

I now can't get your select statements to work.

quote:

Server: Msg 207, Level 16, State 3, Line 58
Invalid column name 'status'.
Server: Msg 207, Level 16, State 1, Line 58
Invalid column name 'ratingID'.
Server: Msg 207, Level 16, State 1, Line 64
Invalid column name 'active'.
Server: Msg 207, Level 16, State 1, Line 64
Invalid column name 'active'.
Server: Msg 207, Level 16, State 1, Line 70
Invalid column name 'active'.
Server: Msg 207, Level 16, State 1, Line 70
Invalid column name 'active'.

I really don't have the time to go back and forth with these errors. Please ensure that you've checked everything before posting.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-07-05 : 14:34:50

Hi Tara,

I apologize for the sloppiness. Your time is much appreciated and I don't want to waste any of it. I'm not sure how but the error that happened occured. I see that it was because some of my commented out lines were included on the select.

I removed this part below

--Where
--active = 1 or active = 4

Theres no room for error in the copy and paste below. If you run the stuff below, you won't see any errors. I've double checked and will hang my head in shame if you do

Been at the computer wayyy to long this shift.

Thanks once again!,
mike123

Here is the updated and double checked working queries

--QUERY #1
--Rows: UserID,NameOnline,EventDate,friendID,friend_nameOnline,typeID

SELECT TOP 100 Ud.UserID, UD.NameOnline, EventDate = F.dateAdded, friendID, UD2.nameOnline as friend_NameOnline, TypeId = 1
from tblFriends F
JOIN tblUserDetails UD ON F.userID = UD.UserID
JOIN tblUserDetails UD2 ON F.FriendID = UD2.UserID
ORDER BY eventDate DESC

--QUERY #2 - (omitted)

--QUERY #3
--Rows: userID,nameOnline,EventDate,counterID,photoID,typeID

SELECT TOP 100 Ud.UserID, UD.NameOnline, EventDate = P.photoDate , TypeId = 3, counterID, photoID
from tblExtraPhotos P
JOIN tblUserDetails UD ON P.userID = UD.UserID
ORDER BY eventDate DESC

--QUERY #4
--Rows: userID,nameOnline,EventDate,typeID

SELECT TOP 100 userID, nameOnline, EventDate = lastUpdated, TypeId = 4
from tblUserDetails UD
ORDER BY eventDate DESC

--QUERY #5
--Rows: userID,nameOnline,EventDate,typeID

SELECT TOP 100 userID, nameOnline, EventDate = date, TypeId = 5
from tblUserDetails UD
ORDER BY eventDate DESC

DaleJ
Starting Member

7 Posts

Posted - 2007-07-05 : 15:15:08

select top 100 *
from
(
SELECT Ud.UserID, UD.NameOnline, EventDate = F.dateAdded, TypeId = 1,
friendID as friend_counterID, null as photoID, UD2.nameOnline as friend_NameOnline
from tblFriends F
JOIN tblUserDetails UD ON F.userID = UD.UserID
JOIN tblUserDetails UD2 ON F.FriendID = UD2.UserID
UNION ALL
SELECT Ud.UserID, UD.NameOnline, EventDate = P.photoDate , TypeId = 3, counterID, photoID, null
from tblExtraPhotos P
JOIN tblUserDetails UD ON P.userID = UD.UserID
UNION ALL
SELECT userID, nameOnline, EventDate = lastUpdated, TypeId = 4, null, null, null
from tblUserDetails UD
UNION ALL
SELECT userID, nameOnline, EventDate = date, TypeId = 5, null, null, null
from tblUserDetails UD
) as data
ORDER BY eventDate DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources