Выбирайте значки из таблицы, только если существуют все строки требований

Question

Я получил три стола. Я хочу запросить таблицу значков, чтобы получить значок, если все миссии существуют для badge_id в badge_requirements Таблица.

В случае ниже, значок будет возвращен, потому что для badge_id = 1Все миссии существуют.

Но если, например, одна запись в finished_missions таблица не будет существовать, тогда значок не будет возвращен.

user_id будет предоставлено из приложения.

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)

РЕДАКТИРОВАТЬ Изменено missions стол к finished_missions для лучшей читаемости. Идентификатор пользователя и идентификатор миссии просто ссылаются на таблицу пользователя и миссии.

РЕДАКТИРОВАТЬ 2 Я попробовал это, учитывая из ответа:

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)

Но он все равно возвращает значок, даже если у меня есть только одна запись в finished_missions Таблица.

Answer 1

Есть много способов. Это должно быть одно:

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

В дополнение к ограничениям, которые вы объявили, также должны быть, UNIQUE(user, mission_id) на finished_missions запретить повторяющиеся записи. Или вы должны использовать count(DISTINCT mission_id) AS ct в первом подзапросе, так что вы можете рассчитывать на счет.

А также UNIQUE(mission_id, badge_id) действительно должен быть ПК - или добавить NOT NULL ограничение для обоих столбцов.

Answer 2

Одним из методов является метод подсчета:

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

Это возвращает badge_id, Если вам нужна дополнительная информация, присоединяйтесь к badges стол или использование in,

И, если нет дубликатов в badge_requirementsзатем используйте count(*) вместо count(distinct),

Answer 3

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

Answer 4

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)

где @userid - параметр SQL.