How do I avoid a repetitive subquery JOIN in SQL?

Question

Hi

In SQL Server 2008:

I have one table, and I want to do something along 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

The two subqueries are exactly identical. Is there any way I can do this without repeating the subquery script?

Thanks

Karl

Answer 1

You can use a View.

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

And then your query would 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 2

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 the question, the code is using derived tables, also known as inline views. A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. See: http://msdn.microsoft.com/en-us/library/aa213252(SQL.80).aspx

Answer 3

Why are they subqueries at all?

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 wouldn't aliasing the table twice work?

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