SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
Thread: Help needed - mysql query
-
Dec 19, 2009, 17:18 #1
- 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
-
Dec 19, 2009, 18:18 #2
- 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).Dr John
www.kidneydialysis.org.uk
-
Dec 19, 2009, 18:29 #3
- 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.
Bookmarks