PDA

View Full Version : Three tables nested count

Zooom
02-15-2007, 11:58 AM
I'm stuck!
I have three tables: Customer, Orders and Items.
If I search on customer I want to display:
1. how many orders that customer has.
2. How many items in these orders that are pending, delivered etc. (the status is specified in Item.item_status)

I want it to look something like this
Cust ID, Name, No Orders, pending item, deliverd item, etc
1234, Smith..,3, 2, 1
1235, Jonson, 1, 3, 2
1236, Olsen, 3, 0, 6

/Mike

02-15-2007, 01:53 PM
you would have to show us the relevant columns in each table of course.

Zooom
02-15-2007, 04:39 PM
I solved it...almost anyway.

02-15-2007, 07:07 PM
then please post the relevant solution here so that others can learn as well.

This is a community, you came here to ask for help. you may not need it this time, but providing a solution now will ensure others will want to help you next time you have trouble and post here.

Zooom
02-20-2007, 08:17 PM
Heres how I did it...

SELECT customer.id AS "CID",
customer.strlast AS "surname",
customer.strfirst AS "firstname",
customer.strEmail AS "email1",
customer.strPhone AS "phone",
customer.strMobile AS "mobile",
sum(case when itemorder.itemStatus = 0 then 1 else 0 end) as New,
sum(case when itemorder.itemStatus = 5 then 1 else 0 end) as Pending,
sum(case when itemorder.itemStatus = 4 then 1 else 0 end) as Unconfirmed,
sum(case when itemorder.itemStatus = 3 then 1 else 0 end) as Confirmed,
sum(case when itemorder.itemStatus = 2 then 1 else 0 end) as Paid,
Min(itemorder.itemdate) as mindate,
Max(itemorder.itemdate) as maxdate
FROM
(customerorder inner join customer
ON customer.id = customerorder.custid)
inner join itemorder ON customerorder.orderid = itemorder.orderid
WHERE
customer.id LIKE pid OR
customer.strlast LIKE pid OR
customer.strfirst LIKE pid OR
customer.strEmail LIKE pid OR
customer.strPhone LIKE pid OR
customer.strMobile LIKE pid
GROUP BY
customer.strlast,
customer.strfirst,
customer.id,
customer.strEmail,
customer.strPhone,
customer.strMobile;

02-21-2007, 01:26 AM
it looks like you are matching an exact phrase pid? if so then you would use = and not like and you would have pid in quotes right?

I'm asking because while you may know that you can do something like this:

where somecolumn IN (1,3,5,7)

you can also do this:

where "pid" in (columnA, columnB, columnC)

might make the code a little easier to read in this case or in a case when you check a bunch more columns. It only works with an exact match and can't use LIKE that is why I asked.