Can a select statement include nested result-sets?

sql, sql-server

Solution

No. This isn't really possible.

SQL Server has no support for nested relations and NF²

Though you could use `FOR XML PATH` to bring it back in a hierarchical manner.

SELECT ID       AS [@ID],
       Customer AS [@Customer],
       (SELECT ID      AS [@ID],
               OrderID AS [@OrderID],
               Product AS [@Product]
        FROM   OrderItems
        WHERE  OrderItems.OrderID = o.ID
        FOR XML PATH('OrderItems'), TYPE)
FROM   Orders o
FOR XML PATH('Order'), ROOT('Orders')

Returns

   <Orders>
      <Order ID="1" Customer="John">
        <OrderItems ID="1" OrderID="1" Product="Guitar" />
        <OrderItems ID="2" OrderID="1" Product="Bass" />
      </Order>
      <Order ID="2" Customer="James">
        <OrderItems ID="3" OrderID="2" Product="Guitar" />
        <OrderItems ID="4" OrderID="2" Product="Drums" />
      </Order>
    </Orders>

This doesn't repeat the parent `Orders`

Problem

Given the following tables and sample data: ``` create table Orders ( ID int not null primary key, Customer nvarchar(100) not null); create table OrderItems ( ID int not null primary key, OrderID int not null foreign key references Orders(ID), Product nvarchar(100) not null); insert into Orders values (1, 'John'); insert into Orders values (2, 'James'); insert into OrderItems values (1, 1, 'Guitar'); insert into OrderItems values (2, 1, 'Bass'); insert into OrderItems values (3, 2, 'Guitar'); insert into OrderItems values (4, 2, 'Drums'); ``` I'd like to find out if I can query the parent `Orders` table and also get the child `OrderItems` table as a nested result-set in the parent result. Something like this: ``` | ORDER.ID | ORDER.CUSTOMER | ORDER.ORDERITEMS | ------------------------------------------------------------------ | | | ORDERITEMS.ID | ORDERITEMS.PRODUCT | | | |------------------------------------- | 1 | John | 1 | Guitar | | | | 2 | Bass | | 2 | James | 3 | Guitar | | | | 4 | Drums | ``` The query I have in mind (which doesn't work in SQL Server) is something like this: ``` -- doesn't work, but shows the intent to have nested result sets select o.OrderID [Order.ID], o.Customer [Order.Customer], (select oi.ID [OrderItems.ID], oi.Product [OrderItems.Product] from OrderItems oi where o.ID = oi.OrderID ) [Order.OrderItems] from Orders o; ``` This is just a conceptual question as I'm trying to think of ways to get related data with minimum duplication (as opposed to what would happen with a `join`, for example). SQL Fiddle here. UPDATE I found out from this answer that Oracle supports it with cursor expressions: ``` select o.*, cursor(select oi.* from OrderItems oi where o.ID = oi.OrderID) as OrderItems from Orders o; ```

Original source