...

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

guelphdad
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.

guelphdad
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;

guelphdad
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum