SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help needed - mysql query

    Guys, i am new to this and i really appreciate your help.

    I have two tables 1) status 2) friends



    please see the above DB image and heres my query

    SELECT message,time FROM status WHERE uid in (SELECT uid2 FROM friend WHERE uid1=10)

    above query returns all message of each friend, i just want only one most recent message. How can i modify this query to get it done??

    and please i want it without using aliasing

    Thanks a lot

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Your table is not normalised, and will always cause you problems.

    Friends should be {uid1, uid2}, and each field should contain just ONE id number. but they should also form a joint key for the table, so that 10,12 is the key to one row, and 10,53 is the key to the next row and 10,9 is the joint key to the third row.

    The data returned by your sub query is the single STRING 12,53,9 and you are comparing the INT uid with this string. (I assume it's an int).

  3. #3
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i gave those table as for sample, they are not real

    Another view:

    SELECT message,time FROM status WHERE uid in (12,53,9);

    with that query, it returns 2 rows for each user, i need latest/most recent one

    SELECT message,time FROM status WHERE uid in (12,53,9) LIMIT 1;

    if i try something like above it gives me only one row

    please, show me how can i modify it so that it will always returns most recent one message for each user.


Tags for this Thread

Bookmarks

Posting Permissions

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