Derived table used several times

Question

I need to run 3 consecutive queries using the same derived table... I'm using MS SQL Server 2008

Select a,b,c from categories inner join (select x,y,z from derivedTable where…) …
Select a,b,c from users inner join (select x,y,z from derivedTable where…) …
Select a,b,c from orders inner join (select x,y,z from derivedTable where…) …

Is there a way to run the 3 sql queries at the same time in a way the derived table

(select x,y,z from derivedTable where ...) 

is executed only once?

I’m using .net so... I’m wondering if I could return the 3 tables and load a dataset with the 3 tables.

Thanks

Answer 1

You could use WITH:

Note: As pointed by @Martin WITH is evaluated several times so the correct solution is the temporary table.

WITH derivedTable (x,y,z)
as
(
    select x,y,z
      from derivedTable
      ...
)
SELECT a, b, c
  FROM users
  join derivedTable on ...
 WHERE ...
union all
SELECT a, b, c
  FROM orders
  join derivedtable on ...
 WHERE ... 
union all
 ...

or a temporary table:

select x,y,z
  into #derivedTable
  from derivedTable
  ...

SELECT a, b, c
  FROM users
  join #derivedTable on ...
 WHERE ...
union all
SELECT a, b, c
  FROM orders
  join #derivedtable on ...
 WHERE ... 
union all
 ...