SELECT dbo.lists_.Name_, COUNT(dbo.members_.EmailAddr_) AS nojgid
FROM dbo.lists_
INNER JOIN dbo.topics_ ON dbo.lists_.Topic_ = dbo.topics_.Title_
LEFT JOIN dbo.members_ ON dbo.members_.List_ = dbo.lists_.Name_
GROUP BY dbo.lists_.Name_,
The first inner join gives you a row for every list.
The left join gives you a row for every (member, list) pair plus a row (with
a null) for every list with no member.
The COUNT will count the non-null things
Note that you want to select/group on dbo.lists_.Name_, not
dbo.members_.List_, which may be null.
HTH
===== Original Message Follows =====
From: "John Berman" <[EMAIL PROTECTED]>
Subject: Count even when empty
Date: Sun, 15 Aug 2004 01:02:52 +0100
Further to my earlier query
Im using this
SELECT dbo.members_.List_, COUNT(dbo.members_.EmailAddr_) AS nojgid
FROM dbo.members_ INNER JOIN
dbo.lists_ ON dbo.members_.List_ = dbo.lists_.Name_
INNER JOIN
dbo.topics_ ON dbo.lists_.Topic_ = dbo.topics_.Title_
WHERE (dbo.members_.jgid IS NULL)
GROUP BY dbo.members_.List_
This works but I want to get a 0 when dbo.members_.jgid IS NULL so my output
shows a 0 for a list, currently is does not show the list when its empty
(obviously because I use is null)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
