select ... count ... inner join statement

Another SQL question for you friendly people out there.

On an ASP page I access a database that contain the following two tables
(simplified here):

Table name: Customers
Columns:
- IDcustomer (primary key)
- Companyname (string)

Table name: Orders

Columns:
IDorder (primary key)
IDcustomer (long integer)
Status (integer)

The first output I wanted - a list indicating the number of order per
customer - was fairly straight-forward:

SELECT C.IDcustomer, C.CompanynameCOUNT(O.IDorder) as c
FROM Customers as as C
INNER JOIN Orders as loan as O
ON CB.IDcustomer = O.IDcustomer
GROUP BY C.IDcustomer, C.Companyname

However, now I want to make list indicating the number of orders which have
been returned (the column Status in Orders contains the value 2 in case the
order has been returned by the customer) by each customer - EVEN IF THAT
NUMBER IS ZERO. I emphasize this last sentence because the above statement
adding "WHERE O.Status = 2" will not do because it will only show customers
which indeed has returned something.

The result should therefore be:

IDcustomer, C
1, Johnson's Hardware Store, 0
2, Paradise Flowers, 0
3, The Red Pub, 2
4, Smith & Sons, 0
(etc)

Any advice would be highly appreciated.

Finally I would like receive recommendations for a good and comprenhensive
book on SQL, containing both real life examples, a complete reference
section, and show differences in implentations for at least Access, MS SQL
and MySQL.

Thanks in advance.

TB

PS: Last week I asked couple of other questions, and did not acknowledge in
time the answers received. Thanks guys, and sorry for not replying earlier.

Re: select ... count ... inner join statement

TB wrote:
> Another SQL question for you friendly people out there.
>
> On an ASP page I access a database that contain the following two
> tables (simplified here):
>
> Table name: Customers
> Columns:
> - IDcustomer (primary key)
> - Companyname (string)
>
> Table name: Orders
>
> Columns:
> IDorder (primary key)
> IDcustomer (long integer)
> Status (integer)
>
> The first output I wanted - a list indicating the number of order per
> customer - was fairly straight-forward:
>
> SELECT C.IDcustomer, C.CompanynameCOUNT(O.IDorder) as c
> FROM Customers as as C
> INNER JOIN Orders as loan as O
> ON CB.IDcustomer = O.IDcustomer
> GROUP BY C.IDcustomer, C.Companyname
>
> However, now I want to make list indicating the number of orders
> which have been returned (the column Status in Orders contains the
> value 2 in case the order has been returned by the customer) by each
> customer - EVEN IF THAT NUMBER IS ZERO. I emphasize this last
> sentence because the above statement adding "WHERE O.Status = 2" will
> not do because it will only show customers which indeed has returned
> something.
> The result should therefore be:
>
> IDcustomer, C
> 1, Johnson's Hardware Store, 0
> 2, Paradise Flowers, 0
> 3, The Red Pub, 2
> 4, Smith & Sons, 0
> (etc)
>
> Any advice would be highly appreciated.

It would help if we knew all the possible values for statusm but given what
you've told us, the solution is as easy as:

WHERE Status IN (0,2)

>
> Finally I would like receive recommendations for a good and
> comprenhensive book on SQL, containing both real life examples, a
> complete reference section, and show differences in implentations for
> at least Access, MS SQL and MySQL.
>

I hope you find one. I've never seen such a beast. :-)
Have you tried searching amazon.com?

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: select ... count ... inner join statement

> It would help if we knew all the possible values for statusm but given
> what you've told us, the solution is as easy as:
>
> WHERE Status IN (0,2)
>


Status can be either 0,1 or 2.

I have just added the line you proposed to my SQL query, but the result is
precisely want do NOT want. Your addition adds a filter, thus not creating
lines where c is 0, i.e. no occurences of returned order from a particular
customer. I would like the COUNT property to count the number of instances
where status = 2, and when that count is 0, ALSO express that (by a zero).
With the filter you propose, instances that return zero are ommited.


>
> I hope you find one. I've never seen such a beast. :-)
> Have you tried searching amazon.com?
>
> Bob Barrows

I found this book:

"SQL in Easy Steps", written by Mike McGrath.
Amazon link:
http://www.amazon.co.uk/exec/obidos/ASIN/1840782579/ref=ord_ cart_shr/026-8153264-1906838?%5Fencoding=UTF8&m=A3P5ROKL5A1O LE

Any opinions?

Thanks again.

TB

Re: select ... count ... inner join statement

TB wrote:
>> It would help if we knew all the possible values for statusm but
>> given what you've told us, the solution is as easy as:
>>
>> WHERE Status IN (0,2)
>>
>
>
> Status can be either 0,1 or 2.
>
> I have just added the line you proposed to my SQL query, but the
> result is precisely want do NOT want. Your addition adds a filter, thus
> not
> creating lines where c is 0, i.e. no occurences of returned order from a
> particular customer.

It shouldn't. it should count lines where status contains either 0 or 2.

> I would like the COUNT property to count the number of
> instances where status = 2, and when that count is 0, ALSO express that
> (by a
> zero). With the filter you propose, instances that return zero are
> ommited.
>

Oh, I see the problem. There are several ways to do this. Here is one:

SELECT C.IDcustomer, C.Companyname, Totals
FROM Customers AS C LEFT JOIN
(select IDcustomer, count(*) AS Totals
FROM Orders
WHERE status in (0,2)
GROUP BY IDcustomer)
AS O ON C.IDcustomer = O.IDcustomer

Unfortunately, if you really want to see the 0's, you are going to need to
use some non-standard functions

Here is how you would do it in Access:

SELECT C.IDcustomer, C.Companyname, Nz(Totals, 0)
FROM Customers AS C LEFT JOIN
(select IDcustomer, count(*) AS Totals
FROM Orders
WHERE status in (0,2)
GROUP BY IDcustomer)
AS O ON C.IDcustomer = O.IDcustomer

In SQL Server, you would use either the COALESCE or ISNULL function. I have
no idea how it would be done in MySQL

Another way to do it in Access would be:

SELECT C.IDcustomer, C.Companyname,
Sum(Iif(Status=1,0,1)) As Totals
FROM Customers AS C INNER JOIN Orders
AS O ON C.IDcustomer = O.IDcustomer
GROUP BY C.IDcustomer, C.Companyname

In SQL Server, you would use CASE instead of Iif. Again, I don't know what
functions are provided by MySQL.

>> I hope you find one. I've never seen such a beast. :-)
>> Have you tried searching amazon.com?
>>
>
> I found this book:
>
> "SQL in Easy Steps", written by Mike McGrath.
> Amazon link:
> http://www.amazon.co.uk/exec/obidos/ASIN/1840782579/ref=ord_ cart_shr/026-8153264-1906838?%5Fencoding=UTF8&m=A3P5ROKL5A1O LE
>
> Any opinions?

Nope. i've never read it. I don't have a SQL Language book in my library.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: select ... count ... inner join statement

> Here is how you would do it in Access:
>
> SELECT C.IDcustomer, C.Companyname, Nz(Totals, 0)
> FROM Customers AS C LEFT JOIN
> (select IDcustomer, count(*) AS Totals
> FROM Orders
> WHERE status in (0,2)
> GROUP BY IDcustomer)
> AS O ON C.IDcustomer = O.IDcustomer

That one does not work. The server returns: "Undefined function 'Nz' in
expression."

> SELECT C.IDcustomer, C.Companyname,
> Sum(Iif(Status=1,0,1)) As Totals
> FROM Customers AS C INNER JOIN Orders
> AS O ON C.IDcustomer = O.IDcustomer
> GROUP BY C.IDcustomer, C.Companyname

That one does work perfectly. Thanks a lot.

TB

Re: select ... count ... inner join statement

TB wrote:
>> Here is how you would do it in Access:
>>
>> SELECT C.IDcustomer, C.Companyname, Nz(Totals, 0)
>> FROM Customers AS C LEFT JOIN
>> (select IDcustomer, count(*) AS Totals
>> FROM Orders
>> WHERE status in (0,2)
>> GROUP BY IDcustomer)
>> AS O ON C.IDcustomer = O.IDcustomer
>
> That one does not work. The server returns: "Undefined function 'Nz'
> in expression."

It works in Access. I forgot Nz was not usable from ASP. Just replace it
with:

Iif(Totals IS Null,0,Totals)

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: select ... count ... inner join statement

Thanks Bob, as always, you have been very helpful.

TB

"Bob Barrows [MVP]" wrote in message
news:eXwJcPQOFHA.2748@TK2MSFTNGP09.phx.gbl...
> TB wrote:
>>> Here is how you would do it in Access:
>>>
>>> SELECT C.IDcustomer, C.Companyname, Nz(Totals, 0)
>>> FROM Customers AS C LEFT JOIN
>>> (select IDcustomer, count(*) AS Totals
>>> FROM Orders
>>> WHERE status in (0,2)
>>> GROUP BY IDcustomer)
>>> AS O ON C.IDcustomer = O.IDcustomer
>>
>> That one does not work. The server returns: "Undefined function 'Nz'
>> in expression."
>
> It works in Access. I forgot Nz was not usable from ASP. Just replace it
> with:
>
> Iif(Totals IS Null,0,Totals)
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>