help needed to sort by multiple timestamps

[lefrank]

I'm fairly new at mysql, things a going but i got thought one ... (to me at least)

to simplify ( i actually have 9 tables):

Let's say I have 3 tables representing 3 different events (like user registration, user login-in, user upgrading ...)

table one
------------------------------------
userid timestamp event1

100 2008-10-24 08:11:37 no
102 2008-10-24 18:11:37 yes

table two
------------------------------------
userid timestamp event2
105 2008-10-24 06:11:37 no
103 2008-10-24 21:11:37 no

table three
------------------------------------
userid timestamp event3
101 2008-10-24 11:11:37 yes
100 2008-10-24 02:11:37 yes

i'm trying to get query to output result sorted based on timestamps :

the final output ( i can handle the php part ... it's the mysql query i'm strugling with ) would look like :

TIME

time of event userid event1 event2 event3
2008-10-24 02:11:37 100 yes
2008-10-24 06:11:37 105 no
2008-10-24 08:11:37 100 no
2008-10-24 11:11:37 101 yes
2008-10-24 18:11:37 102 yes
2008-10-24 21:11:37 103 no

time can be asc or desc ... i'm really trying to get a query that gives me a list of all event from every table but listed as they occurred.

any help would be great!.

thanks
marc

[phranque]

welcome to WebmasterWorld [webmasterworld.com], marc!

you need to use the UNION syntax.
i'm not sure if your columns for the results are formatted properly.

if you want one column of events, try something like:

(SELECT userid timestamp event1 AS event FROM table_one)
UNION
(SELECT userid timestamp event2 AS event FROM table_two)
UNION
(SELECT userid timestamp event3 AS event FROM table_three)
ORDER BY timestamp

if you want three columns of events, you will have to create dummy values for the missing columns, so try something like:

(SELECT userid timestamp event1 '' '' FROM table_one)
UNION
(SELECT userid timestamp '' event2 '' FROM table_two)
UNION
(SELECT userid timestamp '' '' event3 FROM table_three)
ORDER BY timestamp

(those are pairs of single quotes)

le phranque

[lefrank]

Thanks for putting me on the right track !

the only thing i should have mentioned, my timestamp columns are actually named differently in every table ... for example timestamp1 for the events in table 1, timestamp 2 for the events in table 2... and so forth. So i'm a trying to find out how to do the ORDER BY. (or should i rename my columns?)

[phranque]

i know the results should all have the column name of the first select.
did you try sorting on timestamp1?
otherwise, i would try adding the "AS timestamp" after timestamp1, timestamp2, etc in the select statements and keep the "ORDER BY timestamp".