jack jack - 6 months ago 13
MySQL Question

database - Inner join with 3 tables in mysql issue

I'm doing a favorite system which allow users to save the posts to their page.

The tables I'm using are:

saved_posts
table which contain 3 columns (
painned_id``user_id``post_id
)

users
which contain (
user_id
,
frist_name
,
last_name
,
username
,
email
,
password
,
user_website
,
user_avatar
)

Finally
posts
table (
post_id
,
user_id
,
post_author
,
category_id
,
post_date
,
post_image
,
post_avatar
,
user_website
,
post_keywords
,
post_content
)

The my sql code I'm using to get the saved posrt

SELECT * FROM posts INNER JOIN saved_posts
ON saved_posts.post_id =
posts.post_id INNER JOIN users on saved_posts.user_id = users.user_id WHERE
saved_posts.user_id = users.user_id AND saved_posts.post_id = posts.post_id


The problem is that the saved posts from one user appears for other user as if they saved them as well.

Answer

Shouldn't you filter your answer with a WHERE condition? Otherwise it is just finding all posts that any user shared EVER.

SELECT * FROM posts 
INNER JOIN saved_posts  
ON saved_posts.post_id  = posts.post_id
INNER JOIN users 
ON saved_posts.user_id = users.user_id
WHERE users.user_id = :wanted_user_id

Don't forget to bind wanted_user_id.

Comments