query help, select latest status?

[tjburke79]

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?

Members
MemberID
UserName

Friends
MemberID
FriendID

Status
StatusID
MemberID
Text




This query selects all friends

SELECT FriendID, UserName
FROM `Friends`
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

[Emma]

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

SELECT FriendID
FROM 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
FROM Status
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.

[dalecosp]

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.

[Emma]

True. I just assumed this was a let's learn all the things type situation and (s)he's already come across joins, in however is less common in my experience.

[tjburke79]

thanks for the input, i am just trying to figure this out... and i require assistance.

i could do all joins, but there could be more then 1 status per friend, some friends may not have any at all

i am kinda lost at this point

[Emma]

Joins don't have to have every record matching to return results, so people without status won't be a problem. You might find this article useful; a visual explanation of sql joins.

[tjburke79]

but if they have more then one status, the records will be multiplied... and i only want to get the latest status

[tjburke79]

i am still stuck on this, anybody?

[dalecosp]

Would LIMIT be of any use to you, then?

[tjburke79]

if it is sooo easy, i can someone show me an example then?

[dalecosp]

but if they have more then one status, the records will be multiplied... and i only want to get the latest status

{SELECT STUFF BLAH BLAH ORDER BY STATUS DESC LIMIT 1} ??

[tjburke79]

{SELECT STUFF BLAH BLAH ORDER BY STATUS DESC LIMIT 1} ??

That is obvious... and that does not answer the original question. is there anybody on this message board that can help me out?

[Bonesnap]

That is obvious... and that does not answer the original question. is there anybody on this message board that can help me out?

Have you not been reading the thread? There has been all sorts of help offered to you. You even quoted some!

[tjburke79]

this has just been very frustrating, its obvious that {SELECT StatusID, MemberID, Text FROM Status ORDER BY StatusID DESC LIMIT 1} will give me the latest status.

there could be 100's of status's per member id.

i need to select only the latest status from my friend, i want to do this all in one query

not

select my friends

loop {

select latest status

}

[Ashley Sheridan]

You're probably looking for GROUP BY with an ORDER in the query then