Ok, so I have two very similar tables, except that arent the same table.

I want to combine results and sort by date.

So

tableA
sname
srating
sdate

tableB
name
rating
date

combine the results as fname, frating, fdate and sort by fdate DESC

How can I do this?

Thanks

9/21/2007 10:32:33 AM

union and order by

say

(select sname as fname, srating as frating, sdate as fdate from tableA
union select name as fname, rating as frating, date as fdate from tableB)
order by fdate DESC


[Edited on September 21, 2007 at 10:40 AM. Reason : works in msql]

9/21/2007 10:36:11 AM

fyi message_topic.aspx?topic=484972

9/21/2007 10:39:18 AM

yep, UNION tableA with tableB (assuming the data types are the same), ORDER BY fdate. i can't remember if youre going to have to use AS '' with each column name or not...name the columns all the same before you're able to run the union. i think you do.

so

SELECT sname AS 'name', srating AS 'rating', sdata AS 'date'
from tableA
UNION
select *
from tableB

ORDER BY date.


or something like that

9/21/2007 10:40:23 AM

^^

Thats all of 1 page long, and is not the official sql thread, btw.

^^^
^
Thanks guys ... ill throw that in.

Gotta get some small tweaks fixed in my facebook app before I launch

[Edited on September 21, 2007 at 10:44 AM. Reason : .]

9/21/2007 10:43:37 AM

One more thing ... so those 3 peices of data were the only with different names....

How do I still keep the rest of the fields included in the result, as I would get if I just did a Select * from xxx

total of 12 fields I want to keep in the result at the moment.

9/21/2007 10:48:14 AM

without knowing the names and types the easiest way would be to specify them in both select statements so that the union works...

9/21/2007 10:50:52 AM

Ok, fair enough... I think I can get away with just 4 fields in the end anyhow...

Last thing I think ... I need to set a new field called type to be apart of the row, although it is not currently part of the table, just so that I can keep these entries separate...

So type = 's' from the first, type = 'r' from the second ... also, I am doing an inner join with both separate tables....

'comment' is brought in via the inner join with listA, listB

So at this point, I think this is what I need::

(select tableA.ID, comment, sname as fname, srating as frating, sdate as fdate from tableA inner join listA where tableA.ID = listA.ID
union select tableB.ID, comment, name as fname, rating as frating, date as fdate from tableB inner join listB where tableB.ID = listB.ID)
order by fdate DESC

Plus the type=s, type=r inserted wherever that should go ... any idea??

[Edited on September 21, 2007 at 10:58 AM. Reason : ..]

[Edited on September 21, 2007 at 11:00 AM. Reason : .]

9/21/2007 10:57:29 AM

(select ID, comment, sname as fname, srating as frating, sdate as fdate, 's' as ftype from tableA inner join listA where tableA.ID = listA.ID
union select ID, comment, name as fname, rating as frating, date as fdate, 'r' as ftype from tableB inner join listB where tableB.ID = listB.ID)
order by fdate DESC

just putting in the values shouldn't be hard...

is this a query that will be run often? depending on how much data you have, how long the query takes, do you need all the info, etc, you can optimize this for what you are doing. from views, to triggers, to stored procedures. depends on if it begins to slow down i guess.

[Edited on September 21, 2007 at 11:05 AM. Reason : ]

9/21/2007 11:01:11 AM

cool looks easy enough ..

thanks guys

9/21/2007 11:03:15 AM

don't call your fields things like "sname" and "frating" and "tdate". it's code-smell. if it hasn't already, then it will just confuse you later. call the field what it is. if it's a creation date, call it "creation_date".

9/22/2007 12:32:18 AM

^ I agree

9/22/2007 2:41:04 AM

Yeah, that was just for brief conceptual naming purposes.

Not really the name of my fields, fwiw.

Thanks for the tip though

9/23/2007 10:36:56 PM