Выберите, где НЕ IN - кроме

Question

У меня есть две связанные таблицы. Одна из таблиц содержит каждую запись для фотографий, а другая содержит все записи о кошках для каждой фотографии и информацию о пользователе.

photos table:
id
photo (unique filename)
user


cats tables

photo
user
cat

фото

1    photo1.jpg    40000
2    photo2.jpg    40000
3    photo3.jpg    40000
4    photo4.jpg    40001
5    photo5.jpg    40001

коты

photo1.jpg    40000    A
photo2.jpg    40000    A
photo1.jpg    40000    B
photo1.jpg    40000    C
photo2.jpg    40000    P
photo3.jpg    40000    A

Каждой фотографии можно назначить несколько категорий для каждого пользователя.

Я хочу выбрать все фотографии для пользователя 40000, которые не были включены в кошку "P", даже если они могли быть включены в кошку "A" или "B".

Потому что photo2.jpg включен в P cat не должен появляться в результатах поиска. Мой запрос для пользователя 40000 должен дать следующие результаты:

photo1
photo3

Answer 1

Должен быть простой левый / нулевой тест

select
      p.id,
      p.photo
   from
      photos p
         left join cats c
            on p.photo = c.photo
            AND c.cat = 'P'
   where
          p.user = 40000
      and c.photo is null

По сути, левое соединение по-прежнему позволяет квалифицировать все фотографии. Однако, если соединение определенно существует в таблице категорий с тем же фото AND category = 'P', тогда оно будет иметь значение для "c.photo". Поэтому в предложении where я добавил только те, которые НЕ (через null) существуют в таблице cat.

Answer 2

mysql> select * from photos 
       where photo not in (select photo from cats where cat='P') 
       and user = '40000';