CODEWITHSUNDEEP
mysql
Sergio
Sergio

Reputation: 1239

Mysql query for three table with inner join

I need to determine which user (from the second table) was posted the image from the first table as well as whether a particular user was gave a rating of the photograph (from the third table).

The data to be passed to the php page and mysql query is

Mysql query that I've tried looks like:

SELECT images.*, users.name, likes.user
FROM images
INNER JOIN users
  ON users.id = images.user
LEFT JOIN likes
  ON likes.user = '22'
WHERE images.pic_name = 'pic1'
LIMIT 1

table images:

  id    |    pic_name   |    user
-------------------------------
   1    |      Pic1     |     11
   2    |      Pic2     |     22
   3    |      Pic3     |     33

table users:

   id    |     name  |  status
--------------------------------
   11    |    name 1 |    1
   22    |    name 2 |    1
   33    |    name 3 |    1

table likes:

  id   |   pic_name  | user
 ----------------------------
   1   |    pic 1    |  22

The result for user "22" and "Pic1" should look like:

   id    |    name      |    user
-----------------------------------
   1     |    name 1    |    22

The result for user "11" (this user is not rated picture) and "Pic1" should look like:

   id    |    name      |    user
-----------------------------------
   1     |    name 1    |    null

What mysql query would be proper and give the solution for this?

Upvotes: 0

Views: 29

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

Seems you're on the right track, you're just selecting somewhat odd fields and are missing a condition in the left join that matches the picture name;

SELECT images.id, users.name, likes.user
FROM images
JOIN users 
  ON users.id = images.user
LEFT JOIN likes
  ON likes.pic_name = images.pic_name
 AND likes.user = 22
WHERE image.pic_name = 'pic1';

An SQLfiddle to test with.

Upvotes: 1

Related Questions