user1232073
user1232073

Reputation: 21

MySQL: count matching rows in second table

I want to list all teams, then count how many times each team appears in my second table. Some users are not in the second table, so the count would be zero. The problem is when I use the count function it only lists users that are in the second table. How do I count, and list 0 if they dont appear in second table?

$query = "SELECT t.id as id, t.t_name as name, t.t_city as city, (count(pd.rs)) as pd FROM #__bl_regions as r, #__bl_teams as t, #__bl_paid as pd WHERE t.id != 0 AND t.id != 1 AND (t.id IN($teams)) AND  r.id = ".$t_id." AND pd.rs = 1 AND pd.t_id = ".$t_id." ORDER BY t.t_name";
$db->setQuery($query);
$players = $db->loadObjectList();

Tried Left Join

Ok, so because I am including 3 tables I believe I have to use 2 queries. Same thing is still happening, only listing schools with count. #__bl_paid is the table I want to count, #__bl_teams is the table I want to list all.

$query = "SELECT t.id as id FROM #__bl_regions as r, #__bl_teams as t WHERE t.id != 0 AND t.id != 1 AND (t.id IN($teams)) AND  r.id = ".$t_id."  ORDER BY t.t_name";
$db->setQuery($query);
$players1 = $db->loadResultArray();

if ($players1){
  $players2 = implode(",",$players1);
}else{
   $players2 = 0;
}

$query = "SELECT t.id as id, t.t_name as name, t.t_city as city, coalesce((count(pd.rs)),0) as pdc FROM #__bl_paid as pd LEFT JOIN #__bl_teams as t ON pd.t_id = t.id WHERE (t.id IN($players2)) ORDER BY t.t_name";
$db->setQuery($query);
$players = $db->loadObjectList();

Upvotes: 0

Views: 1319

Answers (2)

Matt Fenwick
Matt Fenwick

Reputation: 49085

You need two pieces to get what you want:

  1. an outer join -- left join is the typical MySQL version used
  2. a way to detect if a column is null, and if so, supply a different value. I often use coalesce

An inner join drops rows that don't have matches in the other table; a left join is similar to an inner join, but preserves all the rows in the left table, and supplies columns with null if there's no matching row in the right table.

Here's an example:

select column1, coalesce(column2, 0) as `newcolumn2`
from lefttable
left join righttable
on lefttable.something = righttable.something

What this will do: whenever column2 is null, it will be replaced with 0.

Upvotes: 1

Peter Kiss
Peter Kiss

Reputation: 9319

You should use LEFT JOIN statement instead of INNER JOIN.

Upvotes: 1

Related Questions