Выборка из таблицы на основе двух?

Question

[Евгений Конкин]

Привет всем!

Два дня сижу, никак не могу сделать выборку, наверное опыта не хватает. Вот решил обратиться к вам, спросить у вас ))

К сути!
Есть таблицы
Таблица — films


Таблица — film_actors


Таблица — film_crew


есть еще сопутствующие таблицы persons, career_list и тд, но они не так важны, их можно потом привязать, главное с этими разобраться.
ладно )
Выборка происходит на стр персоны — список фильмов где персона снималась или принимала хоть какое-то участие в фильме
Пытаюсь сделать выборку из всех 3-х таблиц, известен person_id
выборку делаю на основе 2-х таблиц film_actors и film_crew в каждой запись может быть, а может и не быть, в film_crew записей может быть не одна (персона может быть продюсером картины, режиссером и тд)

Наверное как-то запутанно получилось
Пытаюсь вывести список фильмов, вот так
Например мы на стр Мартина Скорсезе


он только режиссер картины

или мы например на стр Леонардо ДиКаприо


он актер, продюсер и сценарист картины (сценариста я от себя добавил для наглядности)

Вот! Помогите )) никак не могу сделать такую выборку
UNION не подходит как я понимаю, так как таблицы разные

Спасибо!

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

SELECT t5.*, GROUP_CONCAT(DISTINCT(t6.career) ORDER BY t6.id ASC SEPARATOR ', ') 
AS career FROM (SELECT t1.id, t1.title, t1.title_en, t1.year, t1.type, t1.premiera_ru, 
t1.premiera_w, t3.career_id, t2.main_role, t2.character 
FROM films t1 
LEFT JOIN film_actors t2 ON t2.person_id = '$person[id]'
LEFT JOIN film_crew t3 ON t3.person_id = '$person[id]'
WHERE (t1.id = t2.film_id OR t1.id = t3.film_id) 
AND (t2.person_id IS NOT NULL OR t3.person_id IS NOT NULL)  ) AS t5
LEFT JOIN person_careers_list t6 ON t6.id = t5.career_id 
GROUP BY t5.id ORDER BY t5.premiera_w DESC

Answer 1

[Евгений Конкин]

Спасибо всем! Сделал сам, конечно же не без вашей помощи, все ваши советы и примеры наводили меня на правильные мысли ))
Кому интересно, вот:

SELECT * FROM films t1
LEFT JOIN film_actors t2 ON t2.person_id = :person_id AND t2.film_id = t1.id
LEFT JOIN (
	SELECT t3.film_id, t3.person_id, GROUP_CONCAT(t4.career ORDER BY t4.id ASC SEPARATOR ', ') AS career 	 	
	FROM film_crew t3
	LEFT JOIN person_careers_list t4 ON t4.id = t3.career_id
	WHERE t3.person_id = :person_id
	GROUP BY CONCAT(t3.film_id, t3.person_id)
) t5 ON t5.film_id = t1.id
WHERE t2.film_id = t1.id OR t5.film_id = t1.id 
ORDER BY t1.premiera_w DESC

Answer 2

[Rsa97]

Предполагая, что таблица `career_list` (`id`, `title`)- справочник профессий

SELECT *
  FROM (
    SELECT `fc`.`film_id`, GROUP_CONCAT(`c`.`title`) AS `titles`
    FROM `film_crew` AS `fc`
    JOIN `career_list` AS `c` 
      ON `fc`.`person_id` = :person_id AND `c`.`id` = `fc`.`career_id`
    GROUP BY `fc`.`film_id`
  ) AS `t`
  JOIN `films` AS `f` ON `f`.`id` = `t`.`film_id`

Comments

[Евгений Конкин]

Спасибо сейчас поробую! ))

[Евгений Конкин]

нет не получается
в вашем варианте если в film_crew нет совпажения то выборка вообще не происходит
а в film_actors возможно есть совпадение
мне надо сделать выборку если хотя бы в одной из таблиц (film_actors и film_crew) есть совпадение
а в film_crew их может быть больше одного

[Rsa97]

Евгений Конкин: Лучшим решением было бы, IMHO, не выделять актёров в отдельную таблицу, а просто добавить в `career_list` записи "Актёр в главной роли" и "Актёр". Но можно и так, как есть:

SELECT *
  FROM (
    SELECT `film_id`, GROUP_CONCAT(`title`) AS `titles`
    FROM (
      SELECT `fc`.`film_id` AS `film_id`, `c`.`title` AS `title`
        FROM `film_crew` AS `fc`
        JOIN `career_list` AS `c` 
          ON `fc`.`person_id` = :person_id AND `c`.`id` = `fc`.`career_id`
      UNION SELECT `film_id`, 'Актёр'
        FROM `film_actors`
        WHERE `person_id` = :person_id
    ) AS `t`
    GROUP BY `film_id`
  ) AS `t`
  JOIN `films` AS `f` ON `f`.`id` = `t`.`film_id`

[Евгений Конкин]

Rsa97: Спасибо)