Mysql Query (where and group)

Question

I have two tables

tbl_activities

 id | activity 
 1  | activity1
 2  | activity2

tbl_sub_activities

id |activityId   | subActivity   | status 
 1 |  1          | subActivity1  | done
 2 |  1          | subActivity2  | done
 3 |  1          | subActivity3  | ongoing
 4 |  2          | subActivity4  | done
 5 |  2          | subActivity5  | done

Scenario:
An activity can have many subactivities.

if status of subactivity are all done, then activity is done,
if status of subactivity still has an ongoing, the activity is ongoing.

Question:
How can I query tbl_activities to have an output like this.

 id | activity  | status
 1  | activity1 | ongoing
 2  | activity2 | done

I've done this using PHP but I want to know how to do this using SQL query.

Olivier Coilland · Accepted Answer · 2012-07-11T10:52:03.530

3

According to your model, you can use this really performant query:

SELECT
    a.id,
    a.activity,
    MAX(s.status) as status
FROM tbl_activities a
INNER JOIN tbl_sub_activities s ON s.activityId = a.id
GROUP BY a.id;

But beware that it is model-dependant : MAX(statuts) will always give ongoing except if all sub-activites are done ;-)

For a less model-dependant query you can consider this pattern:

SELECT
    a.id,
    a.activity,
    SUM(s.status = "ongoing") as NB_ONGOING,
    SUM(s.status = "done") as NB_DONE
FROM tbl_activities a
INNER JOIN tbl_sub_activities s ON s.activityId = a.id
GROUP BY a.id;

edited Jul 11 '12 at 10:52

answered Jul 11 '12 at 10:45

Olivier Coilland

3,088
16
20

You might want to include `a.activity` in the `GROUP BY` just to be more standards adherent. Also, `statuts` is mispelled. Other than those things, this looks great. – Zane Bien Jul 11 '12 at 10:51
Ups, thanks for the spelling Zane :) As for the `a.activity` in the `GROUP BY`, I totally agree with the standard but still hate to do it as I haven't personnally benchmarked the performance issue yet :/ – Olivier Coilland Jul 11 '12 at 10:54
@Zane: There are standards and standards. – ypercubeᵀᴹ Jul 11 '12 at 11:18
A related discussion on the (heated) topic: [Why does MySQL add a feature that conflicts with SQL standards?](http://stackoverflow.com/questions/7594865/why-does-mysql-add-a-feature-that-conflicts-with-sql-standards) – ypercubeᵀᴹ Jul 11 '12 at 11:27

score 1 · Answer 2 · answered Jul 11 '12 at 10:57

1

if you have only two status done and ongoing then this query may works for you

   Select a.id,a.activityid,b.status 
   from tbl_activities a 
   inner join tbl_sub_activities b on a.id=b.activityid 
   Group by a.id order by b.status DESC

answered Jul 11 '12 at 10:57

Ajay Kadyan

1,081
2
13
36

manurajhada · Answer 3 · 2012-07-11T11:12:49.427

1

For a better :

SELECT
    a.id,
    a.activity,
    case when SUM(s.status = "done") = count(s.status)
         then "DONE" else "ONGOING" 
    END
FROM tbl_activities a
INNER JOIN tbl_sub_activities s ON s.activityId = a.id
GROUP BY a.id;

edited Jul 11 '12 at 11:12

answered Jul 11 '12 at 11:01

manurajhada

5,284
3
24
43

I would use a less verbose `IF` instead of `CASE` here. And I'm not really sure that your condition is good. You probably looked for `SUM(s.status = "done") = COUNT(*)` – Olivier Coilland Jul 11 '12 at 11:04

Mysql Query (where and group)

3 Answers3