Select from where NOT IN - except

Question

I have two related tables. One of the tables contains every entry for the photos and the other has all the cat entries for each photo and user info.

photos table:
id
photo (unique filename)
user


cats tables

photo
user
cat

photos

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

cats

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

Each photo can be assigned to more categories for every user

What i want is to select all photos for user 40000 that have not been included in 'P' cat even if they may have been included in 'A', or 'B' cat.

Because photo2.jpg is included in P cat should not appear in the search results. My query for user 40000 should give the folowing results:

photo1
photo3

Please show sample data and what the result should look like, along with the code you have written so far. — AgRizzo, Feb 27 '14 at 13:57
Presumably the column named `cats.photo` contains values that are in `photos.id`. It is not clear what distinction you intend between `photos.user` and `cats.user`. Do you care whether the photos in your result set are in the `A` or `B` category? Or do you only care that they are *not* in the `P` category? — O. Jones, Feb 27 '14 at 13:59

score 0 · Accepted Answer · answered Feb 27 '14 at 18:50

Should be a simple left-join / null test

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

Basically, the left join still allows all photos to be qualified. However, if the join specifically DOES exist in the categories table by the same photo AND category = 'P', then it will have a value for the "c.photo". So in the where clause, I've added to only include those that DO NOT (via null) exist in the cat table.

@valiD, as a newbie, and depending on the growing volume of your content, the other option you selected can be significantly slower performance. Say you have 10,000 photos that are cat 'P' and user 40000 only has 10 photos, this version will only compare to the 10 in question, not the 10,000. But your call on the final answer. — DRapp, Feb 27 '14 at 19:53

score 0 · Answer 2 · answered Feb 27 '14 at 19:30

0

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

answered Feb 27 '14 at 19:30

Nishant Shrivastava

527
3
11

The performance may depend upon the index that you have built, the JOIN above can lead to a lot of duplicate rows affecting performance .. so a subquery is not necessarily bad and a JOIN not always has a better performance .. https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html – Nishant Shrivastava Feb 28 '14 at 03:48

Select from where NOT IN - except

2 Answers2