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