List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 19 1999 6:13pm
Subject:Re: SQL Question - OUTER JOIN? (newbie)
View as plain text  
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

Thread
SQL Question - OUTER JOIN? (newbie)David Christian19 May
  • Re: SQL Question - OUTER JOIN? (newbie)Christian Mack19 May
    • Re: SQL Question - OUTER JOIN? (newbie)David Christian20 May