Получить записи, в которых встречаются 2 условия

Question

Не получается отобрать модели, в которых есть, как минимум 2 цвета. Схема таблиц:

Models (id int)
1
2
3

Colors (id int, color varchar(255), models_id int)
1, red, 1
2, green, 1
3, red, 2
4, yellow, 2
5, green, 3
6, yellow, 4
7, green, 4
8, red, 5
9, red, 5
10, green, 5
11, yellow, 5

Я бы хотел получить Models.id, в котором присутствуют и красный(red) и зелёный(green) цвета одновременно.

В результате, это должны быть записи: 1 и 5, так как только в них встречаются 2 искомых цвета по условию.

Набросал такого рода запрос,

select m.id from Models m
    inner join Colors c on c.models_id = m.id
where c.color = 'red' and m.id in (
        select distinct m.id from Models m
            inner join Colors c on c.models_id = m.id
        where c.color = 'green')

но мне кажется он какимто неправильным.

Answer 1

select m.id
from Models m
where exists (select * from Colors c where c.models_id = m.id and c.color = 'green')
  and exists (select * from Colors c where c.models_id = m.id and c.color = 'red')

Почти дословный перевод sql-команды с английского:

Выбрать id модели из моделей у которых есть запись о зеленом цвете и есть запись о красном цвете

Answer 2

выбрать по цветам, подсчитать количество, оставить там где 2 шт.

WITH data AS (
    SELECT models_id
    FROM colors
    WHERE color IN ('red', 'green')
    GROUP BY models_id
    HAVING count(distinct color) = 2
)
SELECT m.id
FROM models  AS m 
INNER JOIN data AS d on d.models_id = m.id

можно заджойнить таблицу цветов красный на зеленый

SELECT  DISTINCT r.models_id
FROM colors as r 
INNER JOIN colors AS g ON (
        g.models_id = r.models_id 
        AND g.color = 'green'
    )
WHERE r.color = 'red'

БЛОГ НА HUSL

Получить записи, в которых встречаются 2 условия

Ответы (2 шт):