MySQL join / inner join 3 tables

Question

MySQL join / inner join 3 tables

Navigation

#1 by (3 votes)

0

I have 3 related tables.

Users, in which I get the following columns (username).
Submissions, I get the columns (id, title, thumbnail and rate). It also has the user_id column to know which user made the upload.
Favorites, I get the 3 columns (date, id_submission, id_user)

The point is that I am looking at a user's profile and I get their ID, and I would like to show the submissions which you have marked as favorites. More or less works, but not quite right.

SQL Code:
The variable $user[0]["id"] is the ID of the user you are viewing.

$query = "SELECT f.date, s.id, s.title, s.rate, s.thumbnail, u.username
FROM favorites AS f
INNER JOIN submissions AS s
    ON f.id_submission = s.id
INNER JOIN users AS u
    ON f.id_user = ".$user[0]["id"]."
ORDER BY f.date DESC LIMIT 6";

With a user A I have marked as a favorite a submission of user B. But when I am viewing the profile of user A, he shows me twice the same submission.

Something happens with SQL but I'm not sure what. I always do consultations within loops, but I'm trying to improve them and try to do them in one to improve performance.

mysql sql join

asked by Cheshire 28.02.2017 в 12:18

source

1 answer

My Router does not work with webpack2 Stack overflow in the following program

score 3 · Accepted Answer

I think it's better that you remove the inner condition and put it in a where:

$query = "SELECT f.date, s.id, s.title, s.rate, s.thumbnail, u.username
FROM favorites AS f
INNER JOIN submissions AS s
    ON f.id_submission = s.id
INNER JOIN users AS u
    ON f.id_user = u.id
WHERE f.id_user = ".$user[0]["id"]."
ORDER BY f.date DESC LIMIT 6";