David Christian wrote:
>
> <newbie question>
>
> I have a sticky problem that I think I know how to solve with SQL, but am having a
> hard time solving it using MySQL.
>
> I have two tables:
>
> (1) members - member_id, name, phone, address
> (2) entries - entry_id, member_id, title, description, date
>
> I want to show a list of all members and, alongside each member, I want to show a sum
> of the total number of entries they have in the entries table.
>
> I have been using this:
>
> SELECT members.*, count(entries.entry_id)
> FROM members, entries
> WHERE members.member_id = entries.member_id
> GROUP BY members.name;
>
> That works great! It shows:
>
> NAME PHONE ADDRESS ENTRIES
> David 123-123-1234 PO Box 45 3
> John 234-234-2345 1 Main St 7
> Samantha 345-345-3456 14 London Av 1
>
> However, there are also members who have no entries, and I want to show those as
> well:
>
> NAME PHONE ADDRESS ENTRIES
> David 123-123-1234 PO Box 45 3
> John 234-234-2345 1 Main St 7
> Samantha 345-345-3456 14 London Av 1
> Erica 456-456-4567 Route 89 0
> Jason 124-124-1245 3 Maple Road 0
>
> In SQL I would expect something like this to get me close (a left join):
>
> SELECT m.name, m.phone, m.address, count(e.entry_id)
> FROM members m, entries e
> WHERE m.member_id *= e.member_id
> GROUP BY m.name;
>
> And I'm trying to figure out Section 7.12 of the MySQL manual to see how I implement
> it on this platform, but I'm not getting it right. :-)
>
> SELECT m.name, m.phone, m.address, count(e.entry_id)
> FROM members m, entries e
> LEFT JOIN entries ON m.member_id = e.member_id
> GROUP BY m.name;
>
> Am I trying to do things the hard way? I wonder if there's a simpler solution that
> I'm overlooking.
>
> mysql Ver 9.29 Distrib 3.22.19a, for pc-linux-gnu (i686)
>
> Thanks very much in advance for any assistance! :-)
>
> David
Hi David
The solution of your problem is called LEFT JOIN.
Just substitute the JOIN with a LEFT JOIN and it works:
SELECT
m.name
, m.phone
, m.address
, count(e.entry_id)
FROM
members m
LEFT JOIN entries e
WHERE
m.member_id = e.member_id
GROUP BY
m.name
Tschau
Christian
