Hello I have 3 tables:
(This is example of users & posts system)
users: id, username, password
posts: id, user_id, content
post_likes: id, user_id, post_id, date
I want to fetch all the post likes that user made to OTHER users only. User can like all posts (his own post and other user's post).
In other words: I don't want to get his likes to posts that himeself published.
Something like (this is not the syntax, just example to express myself better):
SELECT * FROM post_likes WHERE user_id = 3 AND posts.user_id != 3
Try this one
SELECT * FROM post_likes pl
LEFT JOIN posts p ON pl.post_id=p.id
WHERE pl.user_id = 3 AND p.user_id != 3
Try somethin like this:
SELECT *
FROM users u
INNER JOIN post_likes pl ON (u.user_id = pl.user_id)
INNER JOIN posts p ON (p.id = pl.post_id AND p.user_id <> u.id)
WHERE u.id = ...
You are looking for post_likes of user 3 where the post_id does not represent a post of his own (i.e. where the post_id is NOT IN the set of his own post_ids):
select *
from post_likes
where user_id = 3
and post_id not in (select id from posts where user_id = 3);
You can try with this
SELECT * FROM post_likes pls
INNER JOIN posts p ON pls.post_id=p.id
WHERE pls.user_id = 3 AND p.user_id != 3