SQL Select user

Question

I have the following query that selects a friend request script that shows the profile pic, the hometown, the userid, and the full name of a user that has requested to be friends with a certain user.

SELECT 
 a.hometown, 
 a.first_name, 
 a.uid, 
 a.last_name, 
 b.friend_one, 
 b.friend_two, 
 b.friend_request_id, 
 p.thumbnail 
FROM 
 users a, 
 friend_requester b, 
 profile_pics p 
WHERE 
 a.uid = b.friend_one 
AND b.friend_two = $uid 
AND p.uid_fk = $uid 
ORDER BY b.created DESC LIMIT 5

My problem is that instead of returning 1 user for each query found in the friend_requester table, I return 3 instances for example of the same user with different profile pictures. So if a user has uploaded 3 profile pictures which will be stored as

id=1, profile_pic=profile_pic1.jpg...
id=1 profile_pic=profile_pic2.jpg... 
id=1 profile_pic=profile_pic3.jpg...

I get 3 different boxes of the same user asking to be friends with the logged-in user.

As Hituptony mentioned, I would want to select the latest inserted picture which would be from the 'profile_pics' table in the column 'created'.

• Glen Solsberry: You need to make sure to join p to either a or b. Otherwise, it's a cross join that will match for every row in the previous table.
• David Manheim: gms - No. If the $uid is a key, you don't need to join them, you already guaranteed uniqueness by matching the same static value. The question is whether the optimizer will know that.

Answer 1

GROUP BY Uid_FK, get the max created and INNER join back to the picture table (done in my subquery called "MaxPic")

SELECT a.hometown, 
 a.first_name, 
 a.uid, 
 a.last_name, 
 b.friend_one, 
 b.friend_two, 
 b.friend_request_id, 
 p.thumbnail 
FROM users a
 INNER JOIN friend_requester b 
 ON b.friend_one = a.uid
 INNER JOIN profile_pics p 
 ON p.uid_fk = b.frind_two
 INNER JOIN
 ( SELECT uid_FK, MAX(Created) AS Created
 FROM Profile_Pics
 GROUP BY uid_FK
 ) MaxPic
 ON MaxPic.UID_FK = p.UID_FK
 AND MaxPic.Created = p.Created
WHERE p.uid_fk = $uid

• user1011713: Hi Gareth I'm using $uid as a session_id for the logged-in user so your code works great but it incorrectly brings back the thumbnail that matches the logged-in user's thumbnail rather than the friend requester. How would I fix that?
• GarethD: I think you need to change the where clause to WHERE a.uid = $uid

Answer 2

So which picture do you want? The picture should have a timestamp related to it, maybe you can pull the date of the picture and get the one closest to NOW() or curdate() in order to display the most recent picture. ^^ I dont think you can group by order by, but you should SELECT b.created, so you can see the order from which it is retrieving the pictures.

• user1011713: Sorry, I forgot to mention that. I want the latest inserted picture which would be from the 'profile_pics' table under the column 'created'.

Answer 3

If you can add a new derived table to your join, you can do this:

 SELECT a.hometown, a.first_name, a.uid, a.last_name, 
 b.friend_one, b.friend_two, b.friend_request_id,
 p.thumbnail 
 FROM users a, 
 friend_requester b,
 (Select MAX(Created) as Created, uid_fk from profile_pics) p_date,
 profile_pics p
WHERE 
 a.uid = b.friend_one AND b.friend_two = $uid AND p.uid_fk = $uid 
 AND p_date.Created=p.Created And p_date.uid_fk=p.uid_fk
ORDER BY b.created DESC LIMIT 5

Alternately, you may want to have 'flag' column in your pic table that contains a 1 if the picture is the most recent, and a 0 otherwise, - this type of problem seems like it would come up a lot, and as the database grows, and there are mor old pictures, you will get performance problems if you need to check every entry each time. With a flag, you can index for performance much more easily. Also, this query would simply need an added: where flag=1. Each time you insert a new picture, you make the flag 1, and the flag of all that users other pics you set to 0.

Cheers, David