Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Three tables nested count

    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

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    you would have to show us the relevant columns in each table of course.

  • #3
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I solved it...almost anyway.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #5
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Heres how I did it...

    Code:
    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;
    Last edited by guelphdad; 02-21-2007 at 01:27 AM. Reason: added [code] and [/code] tags

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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:

    Code:
    where somecolumn IN (1,3,5,7)
    you can also do this:

    Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •