Not finding customers without invoices

2005-11-02 Thread Brian Dunning 
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

Thanks!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Not finding customers without invoices

2005-11-02 Thread Alec . Cawley 
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]

Re: Not finding customers without invoices

2005-11-02 Thread Ian Sales (DBA) 

Brian Dunning wrote:

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

Thanks!


- you need a LEFT JOIN. See http://dev.mysql.com/doc/refman/5.0/en/join.html

- ian

--
+---+
| Ian Sales  Database Administrator |
|   |
|  All your database are belong to us |
| ebuyer  http://www.ebuyer.com |
+---+


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Not finding customers without invoices

2005-11-02 Thread Paul Rhodes 
Hi Mike,

You'll need to use a LEFT JOIN instead of an INNER JOIN.

LEFT JOIN returns mismatches between tables.

Try something like:
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

Hope this helps,
Paul


-Original Message-
From: Brian Dunning [mailto:[EMAIL PROTECTED] 
Sent: 02 November 2005 16:22
To: mysql@lists.mysql.com
Subject: Not finding customers without invoices


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

Thanks!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Not finding customers without invoices

2005-11-02 Thread Michael Stassen 

[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]

Re: Not finding customers without invoices

2005-11-02 Thread Brian Dunning 

Thanks very much to all of you!

Obviously I need to learn more about joins. Appreciate the kick in  
the pants.


:)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]