Query Multiple Tables - Union/Order By

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

General SQL Server Forums

Database Design and Application Architecture

Query Multiple Tables - Union/Order By

Author

Topic

xenia_jazz
Starting Member

3 Posts

Posted - 2007-10-25 : 11:21:59

Hi!

I'm trying to get the results from three different tables, where they have some of the same results. I'm only interested in where they match and then trying to order by date (that's in three columns - M, D, Y). I read previous post in 9/07 but the result doesn't seem to order correctly. It does not have any rhyme or reason to the outputed results as it bounces back and forth through Oct, Nov and Dec posting and throughout all three tables. Here's my query below. Any ideas how I can get my ordering correct for all three tables to display all Oct, all Nov and all Dec?

Thanks so much

select date3, date2, date1, who, what
from
(
select date3, date2, date1, who, what from shows
union
select date3, date2, date1, who, what from shares
union
select date3, date2, date1, who, what from soiree
)
a order by date3, date2, date1

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-25 : 11:30:37

Do you want
order by date3, date1, date2

if it's m, d, y?

Are they numeric or character? If character you may need to pad.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

xenia_jazz
Starting Member

3 Posts

Posted - 2007-10-25 : 11:39:05

It's year, month, day and they are numeric.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-25 : 11:51:40

>> It does not have any rhyme or reason
It does have a reason. It will be doing what you tell it to.

If it's year month day then you are ordering by day, month year. Try

order by date1, date2, date3

You have nothing to order te table data so they will be intermingled to get round that

select date3, date2, date1, who, what
from
(
select date3, date2, date1, who, what, seq = 1 from shows
union
select date3, date2, date1, who, what, seq = 2 from shares
union
select date3, date2, date1, who, what, seq = 3 from soiree
)
a order by date3, date2, date1, seq

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

xenia_jazz
Starting Member

3 Posts

Posted - 2007-10-25 : 12:34:06

I'm not too sure I understand the whole seq added to the query. I added it anyhow to the query and now it says unknown column in query.

But you did make me aware of the dates. Date3 is yr, date2 is day and date1 is month. I had it mixed up. Of course, it's always in the details. So, now it's all fixed.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources