<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
