CODEWITHSUNDEEP
sqlsql-server
ℕʘʘḆḽḘ
ℕʘʘḆḽḘ

Reputation: 19375

SQL: how to self-reference the output of an INNER JOIN?

I would like to be able to run a self-inner join on the output of a query. Performing an self INNER JOIN, in the simplest case, is easy:

SELECT *
FROM A a1 INNER JOIN
A a2 ON
a1.key = a2.key

The problem is that I need to do this self-inner join on the output of another inner join. Something like

SELECT * 

FROM DATA.A A INNER JOIN
DATA.B B 
ON A.key = B.key output /* output is the dataset I am interested in */


INNER JOIN

(FROM DATA.A A INNER JOIN
DATA.B B 
ON A.key = B.key output2) /* same code to get output, so that I can self reference */

ON
OUTPUT.key_alt = OUTPUT2.key_alt

Is it possible to do so? I cannot store output in my database.

Upvotes: 1

Views: 42

Answers (1)

SqlZim
SqlZim

Reputation: 38023

In SQL Server:

I prefer to use a common table expression for this sort of thing. It keeps things a more readable in my opinion. 

with cte as (
  select * 
  from data.A as A 
    inner join data.B as B
      on A.key = B.key
)
select ...
from cte as o
  inner join cte as i
    on o.key = i.key

You can do achieve this with standard subqueries though. 

select o.*
from (
  select * 
  from data.A as A 
    inner join data.B as B
      on A.key = B.key
  ) as o
  inner join (
  select * 
  from data.A as A 
    inner join data.B as B
      on A.key = B.key
  ) as i
    on o.key = i.key

Upvotes: 1

Related Questions