SQL join/union help

I'm having trouble coming up with the right query. I am trying to get recent activity of a user on a website. Activities include, ratings, comments, quotes and stories. I want to be able to display the most recent 5 activities for a user but not sure how i should be doing this (guessing i need to use join or union operator). I have something like this:

SELECT user.comments, user.quotes, user.stories, user.ratings FROM comments, quotes, stories, ratings WHERE comments.userid=1 LIMIT 5.

This will return results for me but is there a way i could order them by most recent? And should i be using a JOIN or UNION?

 

Ok i think i figured it out this is what i have.
(SELECT user.comments FROM comments WHERE comments.userid=1 ORDER BY comments.time DESC) UNION
(SELECT user.quotes FROM quotes WHERE quotes.userid=1 ORDER BY quotes.time ASC)

My only question now is that i am using php to pull in this information. Is there anyway i can detect whether the row being returned is coming from the story, quote, comment table? I need a way to determine this so i can modify how the data is displayed.

 

Don't use UNION if you haven't really good reason for it. From performance side is not good solution.

Create new table UserActivity with fields UserActivityID, UserID, ActivityID, ActivityType, in each rate, comment, quotes and stories insert into this table

UserActivityID = identity column
UserID = of course user identifier
ActivityType = 'comment', 'rate' etc.
ActivityID =´id from tables rates, comments etc. based on ActivityType

if you want compare frequency or whatever maybe add some datetime column too to track date time of inserted activity. This will be best for performance.

 

(SELECT user.comments,
'comments' MYTABLE FROM comments 
WHERE comments.userid=1 ORDER BY comments.time DESC) 
UNION
(SELECT user.quotes,
'quotes' FROM quotes 
WHERE quotes.userid=1 ORDER BY quotes.time ASC);

Code (markup):

 

In my point of view you should try this:
SELECT * FROM t1
ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1
WHERE key_part1=constant
ORDER BY key_part2;

SELECT * FROM t1
ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
WHERE key_part1=1
ORDER BY key_part1 DESC, key_part2 DESC;

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows

#

You use ORDER BY with an expression that includes terms other than the key column name:

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;