[EMAIL PROTECTED] wrote:
Brian Dunning <[EMAIL PROTECTED]> wrote on 02/11/2005 16:22:29:
I'm trying to find a list of customers including a count of all their
invoices, but it's not including customers who have no invoices - and
it should. What's broken?
SELECT customers.company, count(invoices.id) as invcount
FROM customers, invoices
WHERE customers.id= invoices.customer_id
GROUP BY customers.id
ORDER BY customers.creation desc
You need what is called a LEFT JOIN, which enforces that every record on
the left of the join must appears even if there is no record on the right
side. In this case the right side is filled with nulls.
Correct.
I think the SQL would be:
SELECT customers.company, count(invoices.id IS NOT NULL) as invcount
FROM customers LEFT JOIN invoices ON customers.id= invoices.customer_id
GROUP BY customers.id
ORDER BY customers.creation desc
Incorrect. COUNT() counts values, but won't count NULLs. Thus,
COUNT(invoices.id IS NOT NULL) will return 1 when there are no invoices, because
invoices.id IS NOT NULL returns 0 when invoices.id is NULL, and 0 is a value
which can be counted. The correct query is
SELECT customers.company, COUNT(invoices.id) AS invcount
FROM customers
LEFT JOIN invoices ON customers.id = invoices.customer_id
GROUP BY customers.id
ORDER BY customers.creation DESC;
When there are no invoices, invoices.id will be NULL, so COUNT() will return 0,
the correct value.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
