Mysql subquery fails, need help :)

Question

I have a table PICTURES:

username varchar(50) 
picture_id varchar(50) 
datetime 

...and I have a table FRIENDS:

user_1 varchar(50) 
user_2 varchar(50)  
datetime 

When you have friends on the website your username goes in user_1, and your friend username's go in user_2. For each new friend a new row...

I want to show the 5 last pictures of the friends of one user (user_1)

so I try

SELECT p.picture_id, p.datetime
FROM pictures AS p
WHERE p.username = (
    SELECT f.user_2
    FROM friends AS f
    WHERE f.user_1 = '(ENTER USERNAME HERE)'
    ORDER BY f.datetime DESC
    LIMIT 5
)
ORDER BY p.datetime DESC;

And as you can see, the subquery return more than one row so... I need your help or suggestions to help me managing this solution!

Answer 1

Try using IN instead of = in WHERE p.username = (. Since you're selecting up to 5 rows = doesn't quite make sense.

SELECT p.picture_id, p.datetime
FROM pictures AS p
WHERE p.username IN (
    SELECT f.user_2
    FROM friends AS f
    WHERE f.user_1 = '(ENTER USERNAME HERE)'
    ORDER BY f.datetime DESC
    LIMIT 5
)
ORDER BY p.datetime DESC;

Answer 2

Try changing the WHERE p.username =(subquery) to WHERE p.username in(subquery)

SELECT p.picture_id, p.datetime FROM pictures AS p WHERE p.username IN (SELECT f.user_2 FROM friends AS f WHERE f.user_1 = '(ENTER USERNAME HERE)' ORDER BY f.datetime DESC LIMIT 5) ORDER BY p.datetime DESC;

Answer 3

I suggest you try a JOIN instead:

SELECT
    p.picture_id, p.datetime
FROM 
    friends AS f 
    INNER JOIN pictures AS p ON f.user_2 = p.username
WHERE
    f.user_1 = '(ENTER USERNAME HERE)'
ORDER BY
    p.datetime DESC
LIMIT 5

This will give you the last 5 pictures from any of user_1's friends

Answer 4

I assume you mean you want the latest 5 pictures from each of the friends, not the latest 5 pictures among all the friends' pictures.

This is one of the greatest-n-per-group problems that appears so frequently on StackOverflow. Normally the problem is to find the top one from each group, but here's how I solve it when you want the top 5 or some other quantity:

SELECT p1.*
FROM friends AS f
JOIN pictures AS p1 ON (f.user_2 = p1.username)
LEFT OUTER JOIN pictures AS p2 ON (p1.username = p2.username
    AND p1.datetime < p2.datetime)
WHERE f.user_1 = ?
GROUP BY p1.picture_id
HAVING COUNT(*) < 5;

Explanation: for each picture p1 that belongs to one of my friends, count the pictures belonging to the same friend and with a more recent datetime. The pictures that are in the most 5 recent must have fewer than 5 other pictures that are more recent.