SQL query to return all media of specific user and check if friend has seen the media

Question

Media Table:

id	user_id
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

Seen Table:

id	user_id	media_id	friend_id
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

Result I want:

user_id	media_id	friend_id
2	1	NULL
2	2	5
2	3	5
2	9	NULL
2	10	NULL
2	13	5

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.

Answer 1

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