CODEWITHSUNDEEP
mysqlsql
Julien
Julien

Reputation: 41

sql multiple count

I have objects training, room and teacher. A training has 0 or several rooms. A training has 0 or several teachers.

Table training (id,name,town)

Table training_room (id, id_training, id_room, enabled)

Table training_teacher (id, id_training, id_teacher, enabled)

I want the list of trainings who belong to a town with the number of enable rooms linked and the number of enable teachers linked.

Where can I write the conditions on enable fields? This is the beginning of my query.

SELECT training.id, 
       training.town , 
       COUNT ( t_r.id_formation),  
       COUNT ( t_t.id_formation)
FROM training
LEFT JOIN training_room t_r
LEFT JOIN training_teacher t_t
WHERE training.town ='X'
GROUP BY training.id

Thank you very much.

Upvotes: 1

Views: 221

Answers (3)

Andriy M
Andriy M

Reputation: 77737

I would advise you against joining your tables like that. As I said in my comment to StieveG's answer, by joining tables in this manner you can end up with what is sometimes called a mini-Cartesian product. Here's an illustrated explanation just in case you need it.

For example, you have 2 rows in training_room matching a row in training, and 3 rows in training_teacher matching the same training row. First the room table is joined, and, as a result, we get two rows for the said training. They contain two distinct rooms, but remember that they also contain the same training, duplicated.

Why is that important? Because the subsequent join with training_teacher is done not against the training table only, but against the result of the join of training with training_room. It merely happens that the join condition contains only a column from training, but the column will be taken from the previous join, not exactly from the table.

So, you will be matching against each of the duplicated trainings and for each one you will get another three, by the number of the matching teachers.

So, in the end, one training will produce 6 rows where 2 rooms will be repeated 3 times and 3 teachers will be repeated twice. Accordingly, both COUNTs will return 6.

One possible solution could be simply to change the the COUNT expressions like this:

…
COUNT (DISTINCT t_r.id),  
COUNT (DISTINCT t_t.id)
…

That would not prevent the joins from producing Cartesian products, but at least the results would be consistent (provided a training can never have duplicate rooms or teachers).

But if I were you I would probably solve it differently, by grouping in subselects and joining the training table to the results of the subselects, like this:

SELECT
  t.id,
  t.town,
  IFNULL(tr.cnt) AS room_count,
  IFNULL(tt.cnt) AS teacher_count
FROM training AS t

  LEFT JOIN (
    SELECT
      id_training,
      COUNT(*) AS cnt
    FROM training_room
    WHERE enabled = 'true'
    GROUP BY id_training
  ) AS tr ON t.id = tr.id_training

  LEFT JOIN (
    SELECT
      id_training,
      COUNT(*) AS cnt
    FROM training_teacher
    WHERE enabled = 'true'
    GROUP BY id_training
  ) AS tt ON t.id = tt.id_training

WHERE t.town = 'X'

Upvotes: 2

Seph
Seph

Reputation: 8703

This should help

SELECT training.id, 
       training.town, 
       COUNT ( SELECT t_r.id_training FROM training_room t_r WHERE t.id = t_r.id_training AND t_r.enabled = 'true') AS training_room_count,  
       COUNT ( SELECT t_t.id_training FROM training_teacher t_t WHERE t.id = t_t.id_training AND t_t.enabled = 'true') AS training_teacher_count
FROM training t
WHERE training.town ='X'

or you can do (does the same thing overall):

SELECT training.id, 
       training.town , 
       COUNT ( t_r.id_training) AS training_room_count,  
       COUNT ( t_t.id_training) AS training_teacher_count
FROM training t
LEFT JOIN training_room t_r ON t.id = t_r.id_training
LEFT JOIN training_teacher t_t ON t.id = t_t.id_training
WHERE training.town ='X'
  AND COALESCE(t_r.enabled, 'true') = 'true'
  AND COALESCE(t_t.enabled, 'true') = 'true'
GROUP BY training.id, training.town

Upvotes: 0

StevieG
StevieG

Reputation: 8729

Assuming I've understood the question:

SELECT training.id, 
       training.town , 
       COUNT ( distinct t_r.id),  
       COUNT ( distinct t_t.id)
FROM training t
LEFT JOIN training_room t_r ON t.id = t_r.id_training AND t_r.enabled = 'true'
LEFT JOIN training_teacher t_t ON t.id = t_t.id_training AND t_t.enabled = 'true'
WHERE training.town ='X'
GROUP BY training.id

Upvotes: 2

Related Questions