Jump to content

Archived

This topic is now archived and is closed to further replies.

cammac

In Need of help with JOIN statement

Recommended Posts

Hi, could any mysql gurus please help with figuring out if and how it's possible to do the following in a single mysql JOIN query:

 

Select from multiple tables for multiple users the time of the latest update for each user - e.g. tables blog (can be multiple posts), photos (can be multiple photos), profile, interests, etc.:

 

user1 - updated blog - 09-21-2005 12:23:53

user3 - added photo - 09-19-2005 11:34:34

user18 - updated profile - 09-18-2005 09:21:39

 

I tried the above with UNION SELECT but was unable to display only the latest update of each user as GROUP BY gave error.

Share this post


Link to post
Share on other sites

here's an example of my union select with 2 users:

 

(SELECT id, "updated profile", updated FROM profile WHERE id='18' OR id='19' ORDER BY updated DESC)

UNION

(SELECT id, "posted blog", post_time FROM blog WHERE uid='18' OR uid='19' ORDER BY post_time DESC LIMIT 0,1)

UNION

(SELECT id, "picture added", added_time FROM pictures WHERE uid='18' OR uid='19' ORDER BY added_time DESC LIMIT 0,1) ORDER BY updated DESC

 

when I add "GROUP BY id" to the end or before last "ORDER BY" it gives error :(

 

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.