Media Table:

Seen Table:

Result I want:

My current query is this:

SELECT *
  FROM media
  LEFT JOIN seen ON seen.media_id=media.id
WHERE media.user_id = 2
UNION
SELECT *
  FROM media
  RIGHT JOIN seen ON seen.media_id=media.id
WHERE media.user_id = 2;

But this query is missing a condition for friend_id = 5 (I cannot figure this out).

Basically what I want to do is get all rows from Media Table where user_id = 2 and check friend_id = 5 from Seen Table for the specific user_id (2) and their media_id.

CodePudding user response：

You can do this by only joing the tables once, but you need to psecify the on clause with the wanted ids

CREATE TABLE Media  (
  `id` INTEGER,
  `user_id` INTEGER
);

INSERT INTO Media 
  (`id`, `user_id`)
VALUES
  ('1', '2'),
  ('2', '2'),
  ('3', '2'),
  ('4', '10'),
  ('5', '5'),
  ('6', '7'),
  ('7', '7'),
  ('8', '1'),
  ('9', '2'),
  ('10', '2'),
  ('11', '1'),
  ('12', '8'),
  ('13', '2');

CREATE TABLE Seen  (
  `id` INTEGER,
  `user_id` INTEGER,
  `media_id` INTEGER,
  `friend_id` INTEGER
);

INSERT INTO Seen 
  (`id`, `user_id`, `media_id`, `friend_id`)
VALUES
  ('1', '2', '2', '5'),
  ('2', '2', '3', '5'),
  ('3', '2', '10', '10'),
  ('4', '10', '7', '5'),
  ('5', '2', '13', '5'),
  ('6', '7', '24', '7'),
  ('7', '7', '23', '9'),
  ('8', '1', '26', '1'),
  ('9', '7', '22', '2'),
  ('10', '9', '19', '2'),
  ('11', '1', '22', '2'),
  ('12', '8', '20', '2'),
  ('13', '20', '14', '5'),
  ('14','2','2', '4');

SELECT m.user_id, m.id as media_id, s.friend_id 
FROM Media m LEFT JOIN Seen s ON s.user_id = m.user_id AND m.id = s.media_id AND s.friend_id = 5
WHERE m.user_id = 2 

user_id | media_id | friend_id
------: | -------: | --------:
      2 |        1 |      null
      2 |        2 |         5
      2 |        3 |         5
      2 |        9 |      null
      2 |       10 |      null
      2 |       13 |         5

db<>fiddle here