how to select this relation?

Hi there,
assume we got two tables A and B.
Both Tables contain names, while B contains a subset of A thus every
name that is in B can also be found in A.
What I want to do is select all names from A and (and this is my
problem) the count of occurrences of every name in Table B.

Example:

A.name:
adam
bob

B.name:
bob


I'm getting as far as this:
select A.name, count(B.name) as times_in_B from A join B on a.name =
b.name;

But:
The resulting relation contains only names that are in both tables, A
and B, meaning it skips adam:

A.name = bob
times_in_B = 1
..
What I want it to do is select all names from A and return a zero as
times_in_B if the name doesn't ocurr in B at all:

A.name = Adam, Bob
times_in_B = 0, 1

How can I do this?

Thanks in advance.

On 15 Mar, 10:50, "T. S." <tstoff...@gmail.com> wrote:
> Hi there,
> assume we got two tables A and B.
> Both Tables contain names, while B contains a subset of A thus every
> name that is in B can also be found in A.
> What I want to do is select all names from A and (and this is my
> problem) the count of occurrences of every name in Table B.
>
> Example:
>
> A.name:
> adam
> bob
>
> B.name:
> bob
>
> I'm getting as far as this:
> select A.name, count(B.name) as times_in_B from A join B on a.name =
> b.name;
>
> But:
> The resulting relation contains only names that are in both tables, A
> and B, meaning it skips adam:
>
> A.name = bob
> times_in_B = 1
> .
> What I want it to do is select all names from A and return a zero as
> times_in_B if the name doesn't ocurr in B at all:
>
> A.name = Adam, Bob
> times_in_B = 0, 1
>
> How can I do this?
>
> Thanks in advance.

Look at LEFT JOIN and IFNULL()