CODEWITHSUNDEEP
phpmysql
peace_love
peace_love

Reputation: 6471

How can I select data from two mySQL tables connected by a third table?

These are my tables:

projects:

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

orders:

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

products:

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

My aim is to find out for each project if all products are "ok". The connection is the orders table.

So this would be my result

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

I have difficulties, this means I am stuck. I do not know how to connect these tables. My approach:

       $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);

Upvotes: 0

Views: 33

Answers (1)

juergen d
juergen d

Reputation: 204904

Group the projects and then check each group if all records are OK or not

   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

This line:

sum(status <> 'ok') = 0

sums up how often this condition is true. It has to be true never to be OK for each group

Upvotes: 2

Related Questions