SELECT DISTINCT(od.InvoiceNo), c.Name AS Contact,
c.Street + ' ' + c.City + ' ' + c.Zip AS Address,
c.TIN, od.DateAdded, od.SONo AS PurchaseNo,
odd.Quantity, i.Name, i.Description,
(SELECT odt.Price FROM Orders_Details odt
WHERE odt.ItemID = i.ItemID AND odt.OrderNo = od.SONo) AS Price,
(SELECT odt.DollarPrice FROM Orders_Details odt
WHERE odt.ItemID = i.ItemID AND odt.OrderNo = od.SONo) AS DollarPrice,
(SELECT odt.Amount FROM Orders_Details odt
WHERE odt.ItemID = i.ItemID AND odt.OrderNo = od.SONo) AS Amount
FROM Orders_Deliveries od
LEFT JOIN Orders o ON o.SONo = od.SONo
INNER JOIN Orders_Deliveries_Details odd ON od.DeliveryNo = odd.DeliveryNo
INNER JOIN Customers c ON odd.CustomerID = c.CustomerID
INNER JOIN Items i ON odd.ItemID = i.ItemID
WHERE od.InvoiceNo='23456'