sql_language thread: Argh! Company sent us bad design.
Argh. Sorry for the lack of reply on this. I swapped my subscription to
Hotmail before I asked the question, and apparently Hotmail thinks this
listserve is junk mail. :)
Thanks for the suggestions, I should have been more clear in how I wanted
the output. I asked for some help from one other place (whose emails
*don't* get auto-deleted) ;) and this is what we came up with, in case
anyone was interested. It's interesting - I knew you could do subselects in
SQL, but I never thought of joining two subselects like what was suggested.
The solution turned out to be incredibly elegant.
Here it is:
SELECT OO.orders_id, CC.contact_ID, OO.quantity, OO.[description],
(SELECT O.orders_id, C.contact_name, C.address1, C.credit_card_number,
O.quantity, O.[description], O.product_key, O.total
FROM tbl_orders O, tbl_contact_order_info C
WHERE O.contact_id = C.contact_id) OO,
(SELECT contact_name, address1, credit_card_number, Min(contact_id) As
GROUP BY contact_name, address1, credit_card_number) CC
WHERE OO.contact_name = CC.contact_name
AND OO.address1 = CC.address1
AND OO.credit_card_number = CC.credit_card_number
ORDER BY contact_ID
----- Original Message -----
// edited for briefness
> Options you have depend on what you want to accomplish and how you want to
> use it:
> ...there are many ways to proceed...
> If there is other aforementioned information, it is a critical component
> to distinguish separate orders without using some interesting looping to
> see whether next order component belongs to the client with the same name
> as the previous one...
> All said, as a start, you may want to consider grouping on client's name
> and address to list out the complete order, or sum of total order
> components to get the total amount ordered.