select f.userid, l.pageid
from friends f
join likes l ON l.userid = f.friendid
LEFT JOIN likes r ON (r.userid = f.userid AND r.pageid = l.pageid)
where r.pageid IS NULL;
Source
What about this one?
SELECT f.user_id, l.page_id
FROM friend f JOIN like l
WHERE l.page_id NOT IN (SELECT page_id FROM like
WHERE user_id = f.user_id)
Sorry I missed the JOIN condition. The query should be like this:
SELECT f.user_id, l.page_id
FROM friend f JOIN like l
ON f.friend_id = l.user_id
WHERE l.page_id NOT IN (SELECT page_id FROM like
WHERE user_id = f.user_id)
SELECT FROM Friend f, Likes l where f.Friend_id = l.user_id and l.page_id not in (select page_id from like where f.user_id = user_id)
create table Users (id int, friend_id int);create table likes (user_id int, page_id varchar(3)); insert into users values(1,2);insert into users values(2,3);insert into users values(1,3);insert into users values(3,2); insert into likes values(1, \’P1\’);insert into likes values(1, \’P2\’);insert into likes values(2, \’P3\’);insert into likes values(3, \’P1\’); select u1.id as uid, l1.page_id as pid from users u1 join likes l1 on u1.friend_id = l1.user_idwhere (u1.id, l1.page_id) not in (select user_id, page_id from likes); Answers 1 P33 P32 P1