0

I have the following table structure:

enter image description here The registration table, indicates the type of client's contract, if is checked (1) it means that the client is directly related to the class table, if not (0), it means that the client is related to a group and the group is related to a class.

EDIT When the control is 1, the id_group on registration table is null, it means that the client don't belong to a group, and have individual classes.

What I need, is to get the following result:

Client_name | 54 classes | 3 with status 0 | 51 with status 1

Where the number of classes is the sum of (classes related directly to registration table (control 1) with classes related to the group table (control 0))

I have this schema working with multiple queries, I load every client, then I load his classes, and count the classes status, but it's taking too much of the server, I need this result in a single database query.

Is there a way to get this result?

wiLLiamcastrO
  • 238
  • 3
  • 13
  • 1
    Try this: `SELECT client.name, COUNT(class.id), COUNT(class.status) FROM client INNER JOIN registration ON (client.id = registration.id) INNER JOIN class ON (class.id_group = registration.id_group AND class.id_registration = registration.id) GROUP BY class.status` – Think Different Aug 22 '16 at 13:50
  • @ThinkDifferent thanks for your answer, I have edited the text to explain to you, why your solution won't work! When the control is 1, the id_group is null, so, I can't join based on this. – wiLLiamcastrO Aug 22 '16 at 13:53
  • You can remove `class.id_group = registration.id_group AND` from the ON clause and it should work. isn't it? – Think Different Aug 22 '16 at 14:38

0 Answers0