I need help with a query and i am completely stuck. i have 3 tables (Members, Friends, Status). what i am trying to do is list my friends, then as well show there latest status if they have a status. members can have many many status or no status at all. i know how to lists my friends, and i know how to get the latest StatusID from the Status table, but no idea how i can make this work.. does anybody have any idea?
This query selects all friends
SELECT FriendID, UserName
LEFT JOIN Members USING (MemberID)
WHERE Friends.MemberID = '1'
This query finds the latest StatusID
SELECT MAX(StatusID) AS Id FROM Status WHERE MemberID = '1' GROUP BY MemberID
To do this you need to stick a couple of queries together, the proper term is 'Nesting'. Firstly create a query to get the IDs of your friends
WHERE MemberID = 3
Then create a query that gets the latest Status from a friend and give their name
SELECT MAX(s.StatusID), s.Text, m.Username
LEFT JOIN Members m ON m.MembersID = s.MembersID
GROUP BY m.MemberID
The final step is to nest these queries together so you only see the status of your friends (and not everyone as that query does). How is this achieved? By using WHERE and IN. I'll let you figure out the final bit, but it's not particularly tricky.
And "nesting" means "nesting subqueries" ... which can be rather hard on the performance of some DB servers. You'd be better off, methinks, to join all 3 tables. Keep in mind you'll need at least two join conditions.
/!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade!/!!!\ ereg() is deprecated --- don't use it!
dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket