query help, select latest status?
Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: query help, select latest status?

  1. #1
    Junior Member
    Join Date
    Dec 2008
    Posts
    14

    query help, select latest status?

    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

  2. #2
    Member
    Join Date
    Oct 2009
    Posts
    97
    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
    Code:
    SELECT FriendID
    FROM Friends
    WHERE MemberID = 3
    Then create a query that gets the latest Status from a friend and give their name
    Code:
    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.

  3. #3
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,623
    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

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  4. #4
    Member
    Join Date
    Oct 2009
    Posts
    97
    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.

  5. #5
    Junior Member
    Join Date
    Dec 2008
    Posts
    14
    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

  6. #6
    Member
    Join Date
    Oct 2009
    Posts
    97
    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.

  7. #7
    Junior Member
    Join Date
    Dec 2008
    Posts
    14
    but if they have more then one status, the records will be multiplied... and i only want to get the latest status

  8. #8
    Junior Member
    Join Date
    Dec 2008
    Posts
    14
    i am still stuck on this, anybody?

  9. #9
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,623
    Would LIMIT be of any use to you, then?
    /!!\ 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

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  10. #10
    Junior Member
    Join Date
    Dec 2008
    Posts
    14
    if it is sooo easy, i can someone show me an example then?

  11. #11
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,623
    Quote Originally Posted by tjburke79 View Post
    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} ??
    /!!\ 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

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  12. #12
    Junior Member
    Join Date
    Dec 2008
    Posts
    14
    Quote Originally Posted by dalecosp View Post
    {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?

  13. #13
    Senior Member
    Join Date
    Mar 2009
    Location
    Canada
    Posts
    795
    Quote Originally Posted by tjburke79 View Post
    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!
    Prison of Mirrors
    Declare variables, not war.

  14. #14
    Junior Member
    Join Date
    Dec 2008
    Posts
    14
    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

    }

  15. #15
    Senior Member
    Join Date
    Aug 2008
    Location
    London, UK
    Posts
    753
    You're probably looking for GROUP BY with an ORDER in the query then
    Ashley Sheridan
    www.ashleysheridan.co.uk

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •