Okay, there may be a very simple answer for this but for some reason I am having difficulty finding it. I need to write a query statement to fill a datagrid (so it has to be one query). Heres a simplified version of my situation. Maybe someone can shed some light on which ways I can join things to make it happen.
I have three tables: Shipments, ShipmentItems, ShipmentContainers. The Shipments table holds information about the shipment (i.e. date leave, driver, expected arrival time, etc). The ShipmentContainers table holds all containers going on that shipment (pallets, road cases, etc). The ShipmentItems table holds the actual items being shipped (plasma screen, LCD Projector, etc). Containers may be shipped empty (no Items) and Items may be shipped without being in a Container. Heres my tables:
Shipments
-------------
*iShipmentID
*DateLeave
...
ShipmentItems
-------------------
*iShipmentItemID
*iItemID (from another table)
*iShipmentContainerID (set to 0 if not in Container; FK from ShipmentContainers)
*iShipmentID (FK from Shipments)
ShipmentContainers
--------------------------
*iShipmentContainerID
*iShipmentID (FK from Shipments)
*iContainerID (from another table)
I need a query which shows all three things -
(1) A listing of ShipmentItems in ShipmentContainers
(2) A listing of ShipmentItems not in ShipmentContainers (Container field in datagrid = null)
(3) A listing of ShipmentContainers with no ShipmentItems (Item field in datagrid is null)
Can anyone help with my SQL statement? I have tried every different join I know and am stumped. Because of the whole triangular relationship going on its not straightforward. Im thinking I have to create temp tables and nest some select statements but my eyes are bugging out from looking at it - HELP!
Jenn
I have three tables: Shipments, ShipmentItems, ShipmentContainers. The Shipments table holds information about the shipment (i.e. date leave, driver, expected arrival time, etc). The ShipmentContainers table holds all containers going on that shipment (pallets, road cases, etc). The ShipmentItems table holds the actual items being shipped (plasma screen, LCD Projector, etc). Containers may be shipped empty (no Items) and Items may be shipped without being in a Container. Heres my tables:
Shipments
-------------
*iShipmentID
*DateLeave
...
ShipmentItems
-------------------
*iShipmentItemID
*iItemID (from another table)
*iShipmentContainerID (set to 0 if not in Container; FK from ShipmentContainers)
*iShipmentID (FK from Shipments)
ShipmentContainers
--------------------------
*iShipmentContainerID
*iShipmentID (FK from Shipments)
*iContainerID (from another table)
I need a query which shows all three things -
(1) A listing of ShipmentItems in ShipmentContainers
(2) A listing of ShipmentItems not in ShipmentContainers (Container field in datagrid = null)
(3) A listing of ShipmentContainers with no ShipmentItems (Item field in datagrid is null)
Can anyone help with my SQL statement? I have tried every different join I know and am stumped. Because of the whole triangular relationship going on its not straightforward. Im thinking I have to create temp tables and nest some select statements but my eyes are bugging out from looking at it - HELP!
Jenn