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
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
But read up on LEFT JOINs.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
