CODEWITHSUNDEEP
phpmysqlsqlinner-join
Tomás Juárez
Tomás Juárez

Reputation: 1514

Selecting an id with inner join

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?

Upvotes: 0

Views: 210

Answers (3)

echo_Me
echo_Me

Reputation: 37233

you could get the photo_id without join like this:

  SELECT   photo_id
  FROM     loves
  WHERE    photo_id in (select photo_id from loves where nick = "userProfile" )
  AND      photo_id in (select photo_id from loves where nick = "userSession" )
  GROUP BY photo_id
  ORDER BY loves.photo_id DESC 
  LIMIT    100

DEMO HERE

Upvotes: 1

atw13
atw13

Reputation: 719

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)

Upvotes: 1

guessimtoolate
guessimtoolate

Reputation: 8642

That part looks weird: 

INNER JOIN loves ON loves.nick = 'userProfileName'

shouldn't it be (assuming there is a nick field on photos table):

INNER JOIN loves ON loves.nick = photos.nick

or I didn't get something here?

Upvotes: 0

Related Questions