CODEWITHSUNDEEP
sqlsql-server-2008joinsubquery
Karl
Karl

Reputation: 5822

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

Upvotes: 4

Views: 3318

Answers (4)

Mark
Mark

Reputation: 833

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

Upvotes: 0

David Hedlund
David Hedlund

Reputation: 129792

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

Upvotes: 0

KM.
KM.

Reputation: 103579

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

Upvotes: 7

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

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

Upvotes: 0

Related Questions