SQL Select user

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'.

Answers


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

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.


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


Need Your Help

MiniTest Error: "NameError: uninitialized constant"

ruby-on-rails ruby rake minitest nameerror

I'm following Michael Hartl's "Ruby on Rails Tutorial: Learn Web Development", and creating the tests that check a user's name and email for validity of length (name as a maximum of 50 chars, email...

Have Excel formulas that return 0, make the result blank

excel worksheet-function

A recurring Excel problem I have is formulas such as INDEX(array,row,column) that return 0 when there's no result, rather than returning blank.