p2p.wrox.com Forums

Need to download code?

View our list of code downloads.

  Return to Index  

sql_language thread: Argh! Company sent us bad design.

Message #1 by "Jeff Coon" <osujeff@h...> on Thu, 7 Mar 2002 11:51:55 -0600
  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.

  - Jeff

  Here it is:

SELECT OO.orders_id, CC.contact_ID, OO.quantity, OO.[description],
OO.product_key, OO.total

  (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
   FROM tbl_contact_order_info
   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
From: <jjboja@h...>
> 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.

  Return to Index