Forum Moderators: open
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
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
le phranque
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?)
