Record that is related to all records of other table in SQL

Question

Let's say we have three tables in a relational database:

Person {id, name}
Obstacle {id, name}
Person_overcomes_obstacle {person_id, obstacle_id}

I want to write a query that tells me if at least one person has overcome all obstacles. Ideas?

Answer 1

SELECT 
    p.name,
    COUNT(DISTINCT oo.obstacle_id) AS OBSTACLES_COMPLETED
FROM 
    person p

    JOIN person_overcomes_obstacle oo
    ON oo.person_id = p.person_id

GROUP BY 
    p.name

HAVING 
    COUNT(DISTINCT poo.obstacle_id) = (SELECT COUNT(id) FROM obstacle)

Answer 2

This SELECT should return the number of obstacles that nobody has overcome. If the count is zero then all the obstacles have been overcome by at least one person.

SELECT count(*)
  FROM (SELECT po.person_id, o.obstacle_id
          FROM Obstacle o
          LEFT OUTER JOIN Person_overcomes_obstacle po
            ON (o.obstacle_id = po.obstacle_id)) t
WHERE t.person_id IS NULL

You could also do this, for the same effect (and possibly better performance):

SELECT count(*)
  FROM Obstacle o
 WHERE NOT EXISTS (SELECT 1
                     FROM Person_overcomes_obstacle po
                    WHERE po.obstacle_id = o.obstacle_id)

EDIT: As pointed out in the comments, the above two queries only prove that there are no obstacles that nobody has overcome, and not that a single individual has overcome all obstacles.

That aside, it should still be possible to prove that a single user has overcome all obstacles without querying the Person table:

SELECT t.personid, count(*)
  FROM (SELECT DISTINCT po.person_id, o.obstacle_id
          FROM Obstacle o
          JOIN Person_overcomes_obstacle po
            ON (o.obstacle_id = po.obstacle_id)) t
 GROUP BY t.persion_id
 HAVING count(*) = (SELECT count(*)
                      FROM obstacle)

Answer 3

I notice that I was the only person to use the natural table alias for Person_overcomes_obstacle!

You need a relational division query for this.

You can either count up the obstacles and the matching records in Person_overcomes_obstacle and return ones where the 2 numbers match or look at it another way as finding people for which there is no obstacle that they haven't overcome.

SELECT p.id, p.name /*Or use COUNT(*) or wrap in Exists 
                     if you don't care about ids and names*/
FROM Person p
WHERE NOT EXISTS
   (SELECT * FROM Obstacle o
    WHERE NOT EXISTS 
    (
       SELECT * FROM Person_overcomes_obstacle poo
       WHERE poo.person_id = p.id and o.id = poo.obstacle_id
    )
)

Answer 4

Use:

SELECT poo.person_id
    FROM PERSON_OVERCOMES_OBSTACLE poo
GROUP BY poo.person_id
    HAVING COUNT(DISTINCT poo.obstacle_id) = (SELECT COUNT(*) 
                                                                        FROM OBSTACLE)

This is an alternative that is less likely to perform well:

SELECT x.person_id
   FROM (SELECT poo.person_id,
                         COUNT(DISTINCT poo.obstacle_id) AS obs_overcome,
                         (SELECT COUNT(*) 
                             FROM OBSTACLE) AS obs_total
                 FROM PERSON_OVERCOMES_OBSTALCE poo
          GROUP BY poo.person_id) x
 WHERE x.obs_overcome = x.obs_total

In either case, you can join to the PERSON table to get more information if you want - or you could run a count on person_id from either query to know how many people completed all the obstacles recorded.