SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting orders which have been fully invoiced? Help with query

    So I'm drawing a blank here... it's Monday! I have two tables - cart and cart_items.

    cart
    ----
    id
    status
    total
    date_created
    date_ordered
    date_shipped

    cart_items
    ----------
    id
    cart_id
    item_id
    item_name
    quantity
    price
    quantity_invoiced


    I would like to select all carts and the cart_items which have been fully invoiced (where cart_items.quantity_invoiced = quantity). If a cart has 3 items, and all 3 items have quantity_invoiced = quantity then I want the three line items to show up in the query results. If a cart has 3 items and only two items have quantity_invoiced = quantity (the third item does not match), I do not want this cart or any of its cart_items to show up. Does that make sense? I'm drawing a blank on how to write this query. Can anyone help?

    Format of results should be something similar to this:

    cart_id | date_ordered | item_name | quantity | price | (qty * price)

    Where I'm drawing the blank is excluding ALL carts from results in which any cart_item record does not match (quantity_invoiced = quantity).
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT c.*
         , ci.*
      FROM ( SELECT cart_id
               FROM cart_items
             GROUP
                 BY cart_id
             HAVING 0 =
                    COUNT(CASE WHEN quantity <> quantity_invoiced 
                               THEN 'uh oh'
                           END)
           ) AS ok
    INNER
      JOIN carts AS c
        ON c.id = ok.cart_id
    INNER 
      JOIN cart_items AS ci
        ON ci.cart_id = c.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! The HAVING clause is what I completely forgot about and was drawing a blank on.
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed


Bookmarks

Posting Permissions

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