MySQL query with 2 COUNT() of other tables with where conditions

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     开发者_JAVA百科        | 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!

---

use left join instead of inner join

select
  sports.id,
  sports.name,
  sports.slug,
  sports.description,
  ifnull(count( distinct seasons.id ), 0) as DistinctSeasons,
  ifnull(count( distinct competitions.id ), 0) as TotalCompetitions
from 
  sports
  left join seasons on sports.id=seasons.id_sport
  left join competitions on seasons.id = competitions.id_season
group by
  sports.id;

---

SELECT sports.*,
    COUNT(seasons.id) AS total_seasons,
    COUNT(competitions.id) AS total_competitions
    FROM sports, seasons, competitions
    WHERE sports.id=id_sport AND seasons.id=id_season
    GROUP BY sports.id

---

select
      sports.id,
      sports.name,
      sports.slug,
      sports.description,
      count( distinct seasons.id ) as DistinctSeasons,
      count( * ) as TotalCompetitions
   from 
      sports 
         left outer join Seasons
            on sports.id = seasons.id_sport,
         left outer join Competitions
            on seasons.id = Competitions.id_season
   group by
      sports.id