CODEWITHSUNDEEP
mysql
Boy Pasmo
Boy Pasmo

Reputation: 8471

counting related fields in two tables

I have these two tables.

foo

+----+--------+
| id | letter | 
| 1  | A      | 
| 2  | B      | 
| 3  | C      |
| 4  | D      |
+----+--------+

bar

+----+------+--------+--------+
| id | name | foo_id | status |
| 1  | fox  | 1      | 1      |
| 2  | cat  | 1      | 0      |
| 3  | dog  | 3      | 1      |
| 4  | bird | 5      | 1      |
| 5  | cow  | 5      | 0      |
+----+------+--------+--------+

Now, I want to count the occurrences of the letter in the bar table with only status equals 1

So LEFT JOIN states

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

This is the result I want.

+--------+-------+
| letter | count |
| A      | 1     |
| B      | 0     |
| C      | 1     |
| D      | 0     |
| E      | 1     |
+--------+-------+

What I have tried so far.

SELECT letter, count(foo_id) AS count
FROM foo f LEFT JOIN bar b on f.id = b.foo_id
GROUP BY letter

I know my query is wrong because it does not filter the status with 1. But when I try to add WHERE status = 1 to my query. Seems off.

What should be the query to achieve my desired result?

Upvotes: 0

Views: 33

Answers (1)

GarethD
GarethD

Reputation: 69759

You need to add your filter to the join predicate:

SELECT letter, count(foo_id) AS count
FROM   foo f 
       LEFT JOIN bar b 
         ON f.id = b.foo_id 
         AND b.status = 1
GROUP BY letter;

Putting it in the where clause will effectively turn your left join into an inner join, because where there is no match in bar, you will end up with WHERE NULL = 1, which is not true, so this record from foo will not be returned.

Upvotes: 1

Related Questions