View Full Version : Three tables nested count
02-15-2007, 11:58 AM
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
02-15-2007, 01:53 PM
you would have to show us the relevant columns in each table of course.
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.
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
(customerorder inner join customer
ON customer.id = customerorder.custid)
inner join itemorder ON customerorder.orderid = itemorder.orderid
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
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.