Jump to content


Photo

In Need of help with JOIN statement


  • Please log in to reply
1 reply to this topic

#1 cammac

cammac
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 30 October 2005 - 03:11 AM

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.

#2 cammac

cammac
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 30 October 2005 - 03:16 AM

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 :(





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users