Get rows only joined to certain types of row in another table

Question

I have rows in a person table. Each can be linked to multiple rows in a thing table via a link table. Each thing has an objectType. I want to get all rows in the person table which are ONLY linked to three specific objectTypes and I only want one row per person. I'm a SQL newbie and I don't know where to start. I've set up a sample:

http://sqlfiddle.com/#!18/56bd69/2

Just using INNER JOINS and WHERE doesn't work because it returns all the person rows.

Answer 1

Untested (seemed to be some technical problem with fiddle), but you might be looking for the Exist predicate

SELECT p.*
FROM person as p
WHERE EXISTS (
    SELECT 1
    FROM link as l
    JOIN thing as t
        ON t.objectID = l.thingObjectId
    WHERE t.objectType IN ('fork','knife','eraser')
      AND p.objectID = l.personObjectId
) ;

EDIT: With info from the comment, it is clear that what you want persons that have all 3 object types (I assumed any). In SQL there is no ALL quantifier (contrary to EXISTS). It is, however, possible to do the transformation:

ALL x:p(x) <=> NOT EXISTS x:NOT x 

which as noted by you in the comment would result in:

SELECT p.*
FROM person as p
WHERE NOT EXISTS (
    SELECT 1
    FROM link as l
    JOIN thing as t
        ON t.objectID = l.thingObjectId
    WHERE t.objectType NOT IN ('fork','knife','eraser')
      AND p.objectID = l.personObjectId
) ;

Another option is to count which persons that have exactly 3 objects among the 3:

 SELECT p.rowkey
 FROM person as p
 JOIN link as l
     ON p.objectId = l.personObjectId
 JOIN thing as t
     ON t.objectID = l.thingObjectId
 WHERE t.objectType IN ('fork','knife','eraser')
 GROUP BY p.rowkey
 HAVING COUNT( DISTINCT t.objectType ) = 3;

These are the two most commonly used techniques for relational division that I know of.

Answer 2

I would just do this by joining to your object table a bunch of times. I think this should give you exactly the persons who are linked to the three specific objects and nothing else (untested, but you should be able to make it work):

SELECT p.* 
FROM Person AS p
JOIN Link AS l1 ON p.id = l1.person_id
JOIN Thing AS t1 ON t1.id = l1.thing_id AND t1.type = 'fork'
JOIN Link as l2 ON p.id = l2.person_id
JOIN Thing AS t2 ON t2.id = l2.thing_id AND t2.type = 'knife'
JOIN Link as l3 ON p.id = l3.person_id
JOIN Thing as t3 ON t3.id = l3.thing_id AND l3.type = 'spoon'
LEFT JOIN (Link as l4 ON p.id = l4.person_id
JOIN Thing as t4 ON t4.id = l4.thing_id AND l4.type NOT IN ('fork', 'knife', 'spoon'))
WHERE t4.id IS NULL

It's not pretty and it doesn't extend to more types of things easily, but it solves the problem you have now. Basically the way it works is that it specifically looks for each individual thing, and then makes a fourth query for "all other things" and demands that it comes out empty.