MySQL query with 2 COUNT() of other tables with where conditions
- by Isern Palaus
Hello,
I've a table called sports that contains a list of list of sports, other called seasons that contains the seasons for a specific sport and competitions that have the competitions of a specific sport and season.
I need one MySQL query to print the list of sports with how much seasons and competitions has each. My tables structure:
sports
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| slug | varchar(45) | NO | | NULL | |
| description | varchar(128) | NO | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
seasons
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| id_sport | int(10) unsigned | NO | MUL | NULL | |
| name | varchar(32) | NO | | NULL | |
| slug | varchar(32) | NO | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
competitions
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| id_season | int(10) unsigned | NO | MUL | NULL | |
| name | varchar(32) | NO | | NULL | |
| slug | varchar(64) | NO | | NULL | |
| description | varchar(128) | YES | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
The result of my query needs to contain: sports.*, total_seasons (SUM of seasons where seasons.id_sport=sports.id) and total_competitions (SUM of competitions where competitions.id_season=seasons.id AND seasons.id_sport=sports.id).
Thank you in advance!
