Re: one-to-many-to-one SELECT?

select

    o.col1, o.col2, o.col3, ...
   ,b.col1, b.col2, b.col3, ...
  ,s.col1, s.col2, s.col3, ...

    orders o
   ,address b
   ,address s
where

   o.order_id = &order_id_value
and

   b.address_id = o.billingAddress_id
and

   s.address_id = o.shippingAddress_id

this is called "aliasing". note that the address table is used twice in the from clause for aliases b and s. the database treats them as if they were different tables. one can use this approach for a self-join on a table as well.

"Sam Penrose" <sam_at_ddmweb.com> wrote in message news:313d365b.0108161152.504f3675_at_posting.google.com...
> I have an orders table that contains a billingAddressID and a
> shippingAddressID, which may or may not be identical.
>
> I need to retrieve each order as a single row containing both
> addresses. If Grandma Jones buys a widget for her grandson Junior, the
> row would look something like:
>
> orderID ... First Name (billing) ... First Name (shipping) ... [etc]
> 999999 ... Grandma ... Junior ... [etc]
>
> The SQL needs to in effect join a single order (as defined by orderID)
> to the address table twice, first on
> orders.billingAddressID=addresses.addressID and then on
> orders.shippingAddressID=addresses.addressID, with the column names
> aliased according to which of the two joins they reference
> ("addresses.firstName AS First Name (shipping)...addresses.firstName
> AS First Name (billing)"). I have no clue as to how to do this in SQL.
>
> Thanks for any help...
Received on Fri Aug 17 2001 - 04:42:44 CEST