Complex SQL query for ShoppingCart

Question

I've been trying to figure out how to query a shopping cart database to find all Orders that contain tangible items (items can be downloadable, therefore, not shipped) that have not been assigned a UPS tracking label. I haven't been able to do it.

The involved tables are as follows:

// dbo.Inventory - details about the individual product being sold
- ProductID int primary - Name nvarchar - IsDownloadable bit -
| 5                     | Awesome Shirt  | 0                  |
| 7                     | An Audio Track | 1                  |

// dbo.ShoppingCart --("ShopID" groups the items in the cart)
- CartID int primary - ProductID int - ShopID char (guid) - Quantity int -
| 2                  | 5             | e854a982c9264a72   | 4            |
| 3                  | 7             | e854a982c9264a72   | 1            |

// dbo.Orders - Order information (shipping address, etc)
- OrderID int primary - ShopID char(x)    - BillingInfoColumns -
| 13                  | e854a982c9264a72  | Name,Address,etc   |

// dbo.Tracking - Shipments' (note: a shipment can contain several items) tracking numbers
- TrackingID int primary - OrderID int - TrackingNumber char(x) -
| 5                      | 13          | Ze5Whatever...         |

// dbo.ShippedItems - Maps a ShoppingCart's shipped items to tracking numbers
- ShippingID int primary - TrackingID int - CartID int - QuantityInShipment int
| 6                      | 5              | 2          | 3                      |

Hopefully the above provides an reasonable approximation of how the DB is designed.

So, to clarify what I think I need:

• SELECT all OrderIDs that have NOT had ALL their tangible items Shipped.

• Non-tangible items are IsDownloadable = 1

• Must take into account the ShoppingCart.Quantity column. If we order 4 t-shirts we may put them in one box (with one UPS tracking label). Then again, we may put 2 per box. Or we may put one pair of jeans with one shirt in one same box (again, with one tracking label)...etc.

I have been concocting crap with endless JOINs and nested WHERE NOT IN (SELECT * FROM)s to no avail. Sadly, I can't seem to wrap my head around it...I'm still waiting for my eureka moment.

I'm relatively new to SQL and database design so any information or (constructive) criticism will be greatly appreciated. Feel free to poke holes in the design of the database itself if you think that will help. :-)

---

// I wish I could run this on my brain right now...
// (Neurons, apparently, are "excitable")
UPDATE Brain SET Neuron = 'Excited' WHERE Cortex = 'SQL'

---

UPDATE

Here is what I came up with thanks to Benoit Vidis. This is the actual query I'm using on my real tables/data:

    SELECT 
        d.OrderID
    FROM
        Person.ShoppingCart c
    JOIN
        Inventory.Item i
    ON
        i.ItemID = c.ItemID
    JOIN
        Orders.Details d
    ON
        d.ShopID = c.ShopID
    LEFT JOIN
        Orders.Shipping s
    ON
        d.OrderID = s.OrderID
    LEFT JOIN
        Orders.ShippedItems si
    ON
        s.ShippingID = si.ShippingID
    WHERE
        i.DownloadableMedia = 0 AND
        d.Billed = 1 AND
        d.Ordered = 1
    GROUP BY
        d.OrderID
    HAVING
        SUM(c.Quantity) > CASE WHEN SUM(si.Quantity) IS NULL THEN 0 ELSE SUM(si.Quantity) END

Answer 1

Can you query dbo.Tracking where TrackingNumber is Null? Would that give you the required information?

Answer 2

You might be able to do it using the HAVING clause. In MySQL, it would give something like:

SELECT 
  c.OrderID,
  SUM(c.Quantity) AS tangible_products_number,
  SUM(s.QuantityInShipment) as shipped_items_number
FROM
( 
  Inventory i,
  ShoppingCart c
)
LEFT JOIN
  ShippedItems s
ON
  c.OrderID = s.OrderID
WHERE
  i.ProductID = c.ItemID AND
  i.IsDOwnloadable = 0 AND
  c.OrderID = t.OrderID AND
  s.CartID = c.ID
GROUP BY
  c.OrderID
HAVING
  SUM(c.Quantity) > SUM(s.QuantityInShipment)

The group by syntax will probably need to be adapted for SQL-Server