How do I avoid a repetitive subquery JOIN in SQL?

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

Answers


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


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

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

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

Need Your Help

QTreeWidget insertTopLevelItem - index given not accurately displayed in Tree?

python qt pyqt qtreewidget qtreewidgetitem

I am unable to properly insert a QTreeWidgetItem at a specific index, in this case I am removing all QTreeWidgetItems from the tree, doing a custom sort on their Date Objects and then inserting the...

Finding a MIME type for a file on windows

c++ windows mime-types

Is there a way to get a file's MIME type using some system call on Windows? I'm writing an IIS extension in C++, so it must be callable from C++, and I do have access to IIS if there is some