SQL Server – Get Rows Joined to Specific Types in Another Table

sql server

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.

Best Answer

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.

Related Question