Ok. This is the way I see it.
SHIP is a child of ORDER but rows may not be present for OrderID because the order may not have shipped. You need the outer join cuz SHIP rows may not be present.
PASS is a child of ORDER but rows may not be present for OrderID so you need the outer join.
ITEM is a child of ORDER and *will* exist so you just need an implicit inner join.
OPTS is a child of ITEM. If options exists they need an inner join. Options not chosen
for the item must not be displayed. Therefore, an outer join is inappropriate.
Code:
SELECT ord.OrderID,
ship.SShipName,
ship.SStreet1,
ship.SStreet2,
ship.SCity,
ship.SStateOrProvince,
ship.SPostalCode,
ship.SCountry,
ship.ShipMethod,
itm.IFullSKU,
itm.IDescription,
itm.IQuantity,
itm.ItemID,
group_concat(Opts.OOptionName) option_names
FROM ORDER_INFO ord
LEFT JOIN ORDER_SHIP_INFO ship
ON ship.SOrderID=ord.OrderID
LEFT JOIN ORDER_PASSTHROUGH pass
ON pass.POrderID=itm.IOrderID,
ORDER_ITEMS itm,
ORDER_OPTIONS opts
WHERE ord.OrderID = itm.IOrderID
AND itm.ItemID = Opts.OItemID
GROUP BY
ord.OrderID,
ship.SShipName,
ship.SStreet1,
ship.SStreet2,
ship.SCity,
ship.SStateOrProvince,
ship.SPostalCode,
ship.SCountry,
ship.ShipMethod,
itm.IFullSKU,
itm.IDescription,
itm.IQuantity,
itm.ItemID
The 'option_names' column result should be a comma delim list of selected options for each order/item.
Note that you dont need the order ID duplicated 4 times. Just grab it once.
See how the Opts.OOptionName is the only column not in the GROUP BY? All the other columns will be the same value for a given OrderID and ItemId. Because of this you will only get them once.