选择内部联接的id

Question

I'm trying to construct an user profile, so I'm showing all her likes from the database. But I want to look if the user that have the active session has liked some of the user profile likes.

So, the table name is loves and the structure is:

photo_id (int)
nick     (varchar)
date     (timestamp)

photos table structure:

photo_id (int)
path     (varchar)
title    (varchar)
category (varchar)
nick     (varchar)
date     (timestamp)

This is how I'm traying to do the query:

SELECT photos.photo_id
FROM photos
INNER JOIN loves ON loves.nick = 'userProfileName'
WHERE loves.nick =  'userWithActiveSession'
AND photos.photo_id = loves.photo_id
ORDER BY loves.photo_id DESC 
LIMIT 100

This query should return all photo ID's that the user with active session have liked with the liked photos from the profile requested user.

EXAMPLE

loves table:

nick         photo_id
userProfile  26  
userProfile  1000  
userProfile  27
userProfile  520
userSession  26  
userSession  680  
userSession  1000

So the query should return only two photos_id (1000 and 26), because both users has liked the same photo_id.

Is there any way to modify this code to do what I want?

Answer 1

So you want all the photos owned by X (photos.nick = X) and liked by Y?

SELECT photos.photo_id FROM photos INNER JOIN loves
ON loves.photo_id = photos.photo_id 
WHERE loves.nick = Y AND photos.nick = X
ORDER BY photos.photo_id DESC LIMIT 100

If you want photos liked by both X and Y then you need to join loves to itself, matching the photo_ids from the two copies of the table to each other, and conditioning that one table's nick matches X and the other's matches Y. (See comments)