How to avoid re-querying JOIN in SQL?

Question

In SQL Server 2008:

I have one table and I want to do something in the following lines:

SELECT T1.stuff, T2.morestuff from ( SELECT code, date1, date2 from Table ) as T1 INNER JOIN ( SELECT code, date1, date2 from Table ) as T2 ON T1.code = T2.code and T1.date1 = T2.date2 

Two subqueries are identical. Is there a way to do this without repeating the script subquery?

thanks

Charles

Answer 1

CTE:

 ;WITH YourQuery AS ( SELECT code, date1, date2 from Table ) SELECT T1.stuff, T2.morestuff from YourQuery T1 INNER JOIN YourQuery T2 ON T1.code = T2.code and T1.date1 = T2.date2 

Fyi

In this question, the code uses views that are also known as inline views. A subquery is a SELECT query that returns a single value and is nested inside SELECT, INSERT, UPDATE, or DELETE statements or inside another subquery. A subquery can be used wherever an expression is allowed. See: http://msdn.microsoft.com/en-us/library/aa213252(SQL.80).aspx

Answer 2

You can use View.

 CREATE VIEW myView AS SELECT code, date1, date2 FROM Table 

And then your request will be something like this:

 SELECT T1.stuff, T2.morestuff FROM myView as T1 INNER JOIN myView as T2 ON T1.code = T2.code and T1.date1 = T2.date2 

Answer 3

Why are they subqueries?

 SELECT T1.stuff, T2.morestuff FROM Table T1 INNER JOIN Table T2 ON T1.code = T2.code and T1.date1 = T2.date2 

Answer 4

Why not flatten the table twice?

 SELECT T1.stuff, T2.stuff FROM Table as T1 INNER JOIN Table as T2 ON T1.code = T2.code and T1.date1 = T2.date2