MySQL: count matching rows in second table

Question

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();

Matt Fenwick · Accepted Answer · 2012-02-27 18:19:30Z

1

You need two pieces to get what you want:

an outer join -- left join is the typical MySQL version used
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.

answered Feb 27, 2012 at 18:19

Matt Fenwick

49.3k24 gold badges130 silver badges198 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1232073 Over a year ago

Ok, so because I am including 3 tables I believe I have to use 2 query's. Same thing is still happening, only listing schools with count.

Peter Kiss · Accepted Answer · 2012-02-27 18:14:22Z

1

You should use LEFT JOIN statement instead of INNER JOIN.

answered Feb 27, 2012 at 18:14

Peter Kiss

9,3292 gold badges25 silver badges38 bronze badges

Collectives™ on Stack Overflow

MySQL: count matching rows in second table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related