Select badges from table only if all requirements rows exist

Question

I got three tables. I want to query the badges table to get the badge if all the missions exists for the badge_id in the badge_requirements table.

In the case below, the badge would be returned because for badge_id = 1, all missions exist.

But if, for example, one record in the finished_missions table would not exist, then no badge would be returned.

The user_id will be supplied from the application.

table badges
+----+------+-------+
| id | name | image |
+----+------+-------+
| 1  | OHYE | path  |
+----+------+-------+
PK(id)

table badge_requirements
+------------+----------+
| mission_id | badge_id |
+------------+----------+
| 3          | 1        |
+------------+----------+
| 5          | 1        |
+------------+----------+
UNIQUE(mission_id, badge_id)
FK(mission_id, missions.id)
FK(badge_id, badges.id)

table finished_missions
+----+---------+------------+
| id | user_id | mission_id |
+----+---------+------------+
| 3  | 221     | 3          | // if any of these record doesn't exist
+----+---------+------------+
| 5  | 221     | 5          | // the badge associated with this mission would not be returned
+----+---------+------------+
PK(id)
FK(user_id, users.id)
FK(mission_id, missions.id)

EDIT Changed the missions table to finished_missions for better readability. The user id and mission id are just referencing the user and mission table.

EDIT 2 I've tried this, given from an answer:

SELECT * FROM badges b
INNER JOIN finished_missions fm ON (fm.user_id = 221)
INNER JOIN badge_requirements br ON (br.mission_id = fm.mission_id AND br.badge_id = b.id)

But it still returns the badge even if I have just one record in finished_missions table.

So, what's the expected result (with the specified table data)? — jarlh, Feb 08 '16 at 14:53
Your tables are not properly defined. For the best answer we'd need to know all constraints, especially NOT NULL, PK, FK and UNIQUE constraints. — Erwin Brandstetter, Feb 08 '16 at 15:15
Your question is unclear. What does `user_id` have to do with anything? — Gordon Linoff, Feb 08 '16 at 15:34
@GordonLinoff: `The user_id will be supplied from the application.` I think it's clear now. — Erwin Brandstetter, Feb 08 '16 at 16:33

score 1 · Answer 1 · answered Feb 08 '16 at 14:55

1

One method is a counting method:

select br.badge_id
from badge_requirements br
group by br.badge_id
having count(distinct mission_id) = (select count(*) from missions);

This returns the badge_id. If you want more information, join back to the badges table or use in.

And, if there are no duplicates in badge_requirements, then use count(*) instead of count(distinct).

answered Feb 08 '16 at 14:55

Gordon Linoff

1,242,037
58
646
786

It seems like I can't supply the users id in here. – Feb 08 '16 at 15:27
@Doodlemeat . . . I think you would just add a `where` clause to the subquery. – Gordon Linoff Feb 08 '16 at 15:39

score 0 · Answer 2 · answered Feb 08 '16 at 15:05

0

select * from badges b
inner join mission m on (m.user_id=@userid)
inner join badge_requirements br on (br.mission_id=m.mission_id and br.badge_id=b.id)

where @userid is SQL parameter.

answered Feb 08 '16 at 15:05

Senad Meškin

13,597
4
37
55

I actually did exactly this query before asking question. The problem was that if just one mission exist, the badge is still returned. – Feb 08 '16 at 15:10
no way, because badhr is connected to mission_id, and mission to user id if there is no record that badge wont be returned. for user. – Senad Meškin Feb 08 '16 at 15:25

Clodoaldo Neto · Answer 3 · 2016-02-08T17:21:03.970

0

select user_id, badge_id
from
    badge_requirements br on b.id = br.badge_id
    inner join
    missions m on m.id = br.mission_id
group by user_id, badge_id
having
    array_agg(distinct br.mission_id order by br.mission_id) =
    array_agg(distinct m.id order by m.id)
where user_id = 221

edited Feb 08 '16 at 17:21

answered Feb 08 '16 at 15:22

Clodoaldo Neto

118,695
26
233
260

Can the user id be supplied here? – Feb 08 '16 at 15:26
I'm sorry, but it has syntax errors with the FROM ... ON – Feb 08 '16 at 15:34

Erwin Brandstetter · Accepted Answer · 2016-02-08T16:19:55.410

0

There are many ways. This should be one:

SELECT badge_id
FROM  (  -- count missions per badge for the given user
   SELECT br.badge_id, count(*) AS ct
   FROM   finished_missions fm
   JOIN   badge_requirements br USING (mission_id)
   WHERE  fm.user_id = 221
   GROUP  BY  1
   ) u  -- count missions per badge total
JOIN (
   SELECT br.badge_id, count(*) AS ct
   FROM   badge_requirements
   ) b USING (badge_id, ct)  -- only badges with the full count of missions

In addition to the constraints you declared there should also be a, UNIQUE(user, mission_id) on finished_missions to disallow duplicate entries. Or you have to use count(DISTINCT mission_id) AS ct in the first subquery, so you can rely on the count.

And UNIQUE(mission_id, badge_id) should really be the PK - or add a NOT NULL constraint for both columns.

edited Feb 08 '16 at 16:19

answered Feb 08 '16 at 16:02

Erwin Brandstetter

605,456
145
1,078
1,228

Great answer! I understand that UNIQUE(user, mission_id) is good to have, but why is PK(mission_id, badge_id) needed on the badge_requirements table? I already have a UNIQUE(mission_id, badge_id). – Feb 08 '16 at 16:37
@Doodlemeat: A PK imposes NOT NULL on all involved columns - UNIQUE does not, see: http://stackoverflow.com/a/20006502/939860. You wouldn't want an entry in `badge_requirements` with `mission_id IS NULL`. That requirement would be impossible to meet. – Erwin Brandstetter Feb 08 '16 at 16:56

Select badges from table only if all requirements rows exist

4 Answers4