Join multiple tables with getting total number of rows from 3rd table

Question

Trying to join 4 - 5 tables at once as of wanted to grab multiple data which is stored in 5 tables on in 5th table i am trying to count total number of players that have been joined the tournament this is working fine but the main problem which I am facing here is when there is not data in the main table it still return me 1 row with all fields as null except total players showing 0 as it shown not return any rows can anyone help em out below is my query

    $getTournamentData = $this->db->select('tournament.*, tournament_meta.meta_title, tournament_meta.meta_description, categories.title AS category, categories.slug AS category_slug, games.game_name, games.slug AS game_slug, count(tournament_players.id) AS total_players');
    $getTournamentData = $this->db->join('categories', 'categories.id = tournament.category_id');
    $getTournamentData = $this->db->join('games', 'games.game_id = tournament.game_id');
    $getTournamentData = $this->db->join('tournament_meta', 'tournament_meta.post_id = tournament.id');
    $getTournamentData = $this->db->join('tournament_players', 'tournament_players.tournamentID = tournament.id', 'left');


    $dataCond['created_by'] = $this->session->userdata('user_id');

    if($id != null) {
        $dataCond['tournament.id'] = $id;
    }

    $getTournamentData = $this->db->where($dataCond);
    $getTournamentData = $this->db->get('tournament');   

so in return total_players are showing 0 and rest all is null because no data is insterted in the table yet show it should not return any data from the database

Answer 1

You're mixing an aggregate function (count()) with plain column names in your SELECT clause and that is giving unexpected results. See: Why can't you mix Aggregate values and Non-Aggregate values in a single SELECT?

You can fix this by adding a GROUP BY clause with all of the column names from the SELECT clause, except for the column name that has the count() on it. Be sure to fully type out all of the column names for the tournament.* in the GROUP BY, so use tournament.id, tournament.category_id, tournament.game_id etc instead:

SELECT tournament.*, tournament_meta.meta_title, tournament_meta.meta_description, categories.title AS category, categories.slug AS category_slug, games.game_name, games.slug AS game_slug, count(tournament_players.id) AS total_players
FROM tournament
JOIN categories ON categories.id = tournament.category_id
JOIN games ON games.game_id = tournament.game_id
JOIN tournament_meta ON tournament_meta.post_id = tournament.id
JOIN tournament_players ON tournament_players.tournamentID = tournament.id
GROUP BY
tournament.id, tournament.category_id, tournament.game_id,
-- add other tournament colums here --
tournament_meta.meta_title, tournament_meta.meta_description, categories.title, categories.slug, games.game_name, games.slug

In CodeIgniter (3) this would translate to:

$this->db->select('tournament.*, tournament_meta.meta_title, tournament_meta.meta_description, categories.title AS category, categories.slug AS category_slug, games.game_name, games.slug AS game_slug, count(tournament_players.id) AS total_players');
$this->db->from('tournament');
$this->db->join('categories', 'categories.id = tournament.category_id');
$this->db->join('games', 'games.game_id = tournament.game_id');
$this->db->join('tournament_meta', 'tournament_meta.post_id = tournament.id');
$this->db->join('tournament_players', 'tournament_players.tournamentID = tournament.id');
$this->db->group_by('tournament.id, tournament.category_id, tournament.game_id,
/* add other tournament columns here */
tournament_meta.meta_title, tournament_meta.meta_description, categories.title, categories.slug, games.game_name, games.slug');

Alternatively you can use a subselect, in which case you can remove the join to the tournament_players table:

SELECT tournament.*, tournament_meta.meta_title, tournament_meta.meta_description, categories.title AS category, categories.slug AS category_slug, games.game_name, games.slug AS game_slug, (
    SELECT count(id)
    FROM tournament_players
    WHERE tournament_players.tournamentID = tournament.id) AS total_players
FROM tournament
JOIN categories ON categories.id = tournament.category_id
JOIN games ON games.game_id = tournament.game_id
JOIN tournament_meta ON tournament_meta.post_id = tournament.id

Use with $this->db->query() in CodeIgniter.

I haven't tested these queries obviously, so there may be errors. Hopefully this'll help you get started.