Restricting results of a many to many joined query

Question

I have three tables like this (simplified version):

Object
---------------
ID | ObjectName
---------------

Category
-----------------
ID | CategoryCode
-----------------

ObjectCategory
---------------------
ObjectID | CategoryID
---------------------

How can I select ObjectName and CategoryCode for all objects who don't belong to certain CategoryCodes? I can't just add a WHERE clause to the JOINed query on all three tables as it will ignore the intended restriction as long as an object belongs to at least one allowed CategoryCode.

EDIT: I also need to select CategoryCode from Category table, a select only on Object table is not enough

Answer 1

SELECT ObjectName 
FROM Objects 
    WHERE ObjectID NOT IN (
           SELECT ObjectID 
           FROM ObjectCategory AS a 
           JOIN Category AS b 
                ON a.CategoryID = b.CategoryID
           WHERE CategoryCode = 'yourcodehere')

Haven't tested it, but this should get the names of all the ID's of the Objects that are not connected to a certain CategoryCode

Answer 2

Here's a solution for NOT EXISTS

select       o1.*, c1.*
from         object            o1
inner join   object_category   oc1
on           o1.id           = oc1.object_id
inner join   category c1
on           oc1.category_id = c1.id
where not exists (
            select null
            from       object_category oc2
            inner join category        c2
            on         oc2.category_id = c2.id
            where      c2.name in ('code1',  'code1')
            and        oc2.object_id = o1.id
)

With little effort this can be rewritten to an equivalent NOT IN subquery (not shown)

In mySQL, subqueries, especially correlated subqueries like the EXISTS and NOT IN solutions can be quite slow. Alternative is to try a LEFT JOIN and a GROUP BY:

select      o1.*, c1.*
from        object                 o1
inner join  object_category        oc1
on          o1.id = oc1.object_id
inner join  category               c1
on          oc1.category_id = c1.id
left join   object_category        oc2
on          o1.id = oc2.object_id
left join   category               c2
on          oc2.category_id = c2.id
and         c2.name in ('code1', 'code1' )
group by    o1.id, c1.id
having      count(c2.id) = 0