####derived table, example from Northwind

select c.companyname
from customers c
join
(select customerid
from orders o
join [order details] od
on o.orderid=od.orderid
join products p
on od.productid=p.productid
where p.productname='chocolade') spen
on c.customerid=spen.customerid

join
(select customerid
from orders o
join [order details] od
on o.orderid=od.orderid
join products p
on od.productid=p.productid
where p.productname='vegie-spread') spap
on c.customerid=spap.customerid

#########################derived table, example from Summit 2005

SELECT IB.OID,IB.Parent_OID,IB.StartDate,IB.EndDate,IB.EvtType,T.Description,T2.Description,
IB.EvtCategory,C.Description,C2.Description,ED.Title,ED.Description,S.IndividualID,
dir.firstname,dir.lastname

FROM IBEvent IB
LEFT OUTER JOIN EventDescription ED
ON IB.EventDescription_OID = ED.OID

LEFT OUTER JOIN EventCategories C
ON IB.EvtCategory = C.ID
LEFT OUTER JOIN EventTypes T
ON IB.EvtType = T.ID

LEFT OUTER JOIN EventCategories C2
ON ED.EvtCategory = C2.ID
LEFT OUTER JOIN EventTypes T2
ON ED.EvtType = T2.ID

LEFT OUTER JOIN EventSpeaker S
ON IB.OID = S.IBEvent_OID

JOIN (
SELECT e.ID,e.Description,ed.evtType
FROM EventTypes e
join eventdescription ed
on e.ID = ed.evtType
where e.Description = 'User Summit'
) ET
ON ET.ID = IB.Parent_OID

LEFT OUTER JOIN ISSDBC.ibi_directory.dbo.ISS_Directory Dir
ON Dir.global_id = S.IndividualID

WHERE ET.Description = 'User Summit' AND IB.OID <> 'default'
order by IB.StartDate,IB.OID,C.Description,ED.Title