Как получить записи, упоминание о которых есть в другой таблице?

Question

[SET1]

На новостном сайте у публикаций есть обложки (превью), а в контенте может быть загружено одно или несколько видео. url обложек и видео хранятся в одной таблице - tbl_publication_media, а название и id публикации - в таблице tbl_publication. Мне нужно получить имя и обложку тех публикаций, у которых есть одно или более видео. В ответе должны быть имя и url обложки. Если обложки нет, то null.

Должен быть такой ответ:

name preview
публикация №1 /uploads/preview/...
публикация №2 null <- у нее нет обложки, но видео есть.

структура таблицы tbl_publication_media такая:

- publication_id
- media_type (4 - это обложка, 2 - видео)
- media_url

Answer 1

[Rsa97]

SELECT `p`.`name` AS `pub_name`, `c`.`media_url` AS `cover_url`
  FROM (
    SELECT DISTINCT `publication_id`
      FROM `tbl_publication_media`
      WHERE `media_type` = 2
  ) AS `v`
  JOIN `tbl_publication` AS `p`
    ON `p`.`id` = `v`.`publication_id`
  LEFT JOIN `tbl_publication_media` AS `c`
    ON `c`.`publication_id` = `p`.`id` AND `c`.`media_type` = 4