2

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.

5

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.

  • 2
    Your first EXISTS query will select rows that also do not have all three. – ErikE Nov 30 '19 at 16:57
  • 1
    Your second query (with NOT EXISTS) also returns persons which do not have any of the objects. Only the third query returns the persons which have all three objects (and possibly other objects). – Razvan Socol Nov 30 '19 at 18:26
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.