php - 如何从第三个表连接的两个 mySQL 表中选择数据？

Question

这些是我的表格:

项目:

+----+---------+
| id | project |
+----+---------+
|  1 | cat     |
|  2 | bird    |
|  3 | frog    |
+----+---------+

订单:

+----+------------+
| id | project_id |
+----+------------+
| 21 | 1          |
| 22 | 1          |
| 23 | 2          |
| 24 | 3          |
+----+------------+

产品:

+----+----------+---------+
| id | order_id | status  |
+----+----------+---------+
|  1 |       21 | pending |
|  2 |       21 | ok      |
|  3 |       23 | ok      |
|  4 |       23 | ok      |
|  5 |       22 | ok      |
+----+----------+---------+

我的目标是找出每个项目的所有产品是否都“正常”。连接是订单表。

这就是我的结果

  CAT = SOME PENDING
  BIRD = ALL OK
  FROG = ALL OK

我遇到了困难，这意味着我陷入了困境。我不知道如何连接这些表。我的做法:

       $pdo = $db->query('SELECT  * 
       (SELECT COUNT(*) 
       FROM products 
       WHERE projects.project_id=orders.project_id) AS all,
       (SELECT SUM(`status`=`ok`) 
        FROM products 
        WHERE projects.id=orders.project_id) AS ok,
        FROM projects
        group by projects.id
      ')->fetchAll(PDO::FETCH_ASSOC);

Answer 1

将项目分组，然后检查每个组的所有记录是否正常

   SELECT pj.id, pj.project, 
          case when sum(status <> 'ok') = 0 
               then 'all ok'
               else 'pending'
          end as overall_status
   FROM projects pj
   LEFT JOIN orders o on pj.id = o.project_id
   LEFT JOIN products pr on pr.order_id = o.id
   GROUP BY pj.id, pj.project

这一行:

sum(status <> 'ok') = 0

总结了该条件成立的频率。它必须是true，而不是每个组都可以