• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 895
  • Last Modified:

MySQL COUNT on foreign key

I'm looking for a _single_ MySQL statement to return counts for all values of a foreign key, and return zeros where the no row exists having the foreign key.

This apparently can't be accomplished with an outer join.  The following query does not produce the results I'm after.  Instead of 0 where FK_ID does not exist in TABLE_1, it returns 1.
SELECT TABLE_2.FK_ID, COUNT(*)
FROM TABLE_1 RIGHT OUTER JOIN TABLE_2 ON TABLE_1.FK_ID=TABLE_2.FK_ID
GROUP BY TABLE_2.FK_ID;

Given these tables:
TABLE_1
ID | FK_ID
--------
1  | 1
2  | 1
3  | 2

TABLE_2
FK_ID
---
1
2
3

The result I'm looking for would look similar to the following.
FK_ID | COUNT(*)
-----------------
1       |  2
2       |  1
3       |  0

Help is much appreciated.
0
berk0081
Asked:
berk0081
  • 2
1 Solution
 
Aleksandar BradarićSoftware DeveloperCommented:
Try this:
---
SELECT t2.FK_ID, sum(IF(t1.ID IS NULL, 0, 1)) as CNT FROM TABLE_2 t2 LEFT JOIN TABLE_1 t1 ON t1.FK_ID = t2.FK_ID GROUP BY t2.FK_ID;
---
0
 
awking00Commented:
You need the count of table_1's fk_id not count(*), which gives you the total count of the join -
select t2.fk_id, count(t1.fk_id)
from table_2 t2
left outer join table_1 t1
on t2.fk_id = t1.fk_id
group by t2.fk_id;
0
 
berk0081Author Commented:
Thanks leannonn, that is exactly what I needed.
0
 
awking00Commented:
I probably shouldn't have taken the time to explain why count(*) didn't work :-(
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now