Re: Nested Relations / RVAs / NFNF

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Wed, 27 Oct 2004 07:22:17 -0400
Message-ID: <ad0olc.vhc.ln_at_mercury.downsfam.net>

Marshall Spight wrote:

>
> What if we have:
>
> create table Orders
> (
> OrderId int primary key,
> -- other attributes
> );
>
> create table OrderItems
> (
> OrderItemId int primary key,
> OrderId int references Orders(OrderId),
> Cost money;
> -- other attributes
> );
>
> Then, if we had "pseudo nesting" or "virtual RVAs" we could
> see the attributes of Orders as
> 1) OrderId int
> 2) ...
> 3) OrderItems.OrderItemId int
> 4) OrderItems.Cost money
>

In the simple case, this could be automatic, based on the declaration of the foreign key. If a foreign key appears twice there'd have to be a way to avoid naming collissions.

But actually this is quite smooth. If we allow these virtual tables to chain together, can we do this:

Select customer.customer_name,

       SUM(customer.orders.order_total),
       SUM(customer.orders.orders_detail.extended_price)

  FROM customer
 WHERE customer.orders.orders_detail.item_id = 'GONKULATOR'

Which out to give this report:

Customer Name     Total Orders      Gonkulator Orders
=============     =============     ==================
John Smith              $500.00                $175.00

...and going one further, if the FROM clause contained only customer, could we make the syntax a little nicer:

Select customer_name,

       SUM(.orders.order_total),
       SUM(.orders.orders_detail.extended_price)

  FROM customer
 WHERE .orders.orders_detail.item_id = 'GONKULATOR'

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me

Received on Wed Oct 27 2004 - 13:22:17 CEST