CODEWITHSUNDEEP
sqlsql-servert-sql
Felice Pollano
Felice Pollano

Reputation: 33262

Symmetric cross join

I'm tryng to extract all pair say i,j from each element in a table against each element on the same table, here my query:

select a.Id L,b.id R into #cross from MyTable a cross join mytable b

I'm in the situation where i,j == j,i so just half the record are needed. My naive attempt is:

select a.Id L,b.id R into #cross from MyTable a cross join mytable b 
where not exists
    (select * from #cross c where c.L=R and c.R=L)

but I can't query the destination table while inserting in, as said by SQL Server:

The SELECT INTO statement cannot have same source and destination tables

how can I do in an efficient way ?

EDIT Just for reference, I said "I need half the records", that is wrong, the record count after taking in account that i,j == j,i is n*(n+1)/2

Upvotes: 5

Views: 3006

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

So, just condition the join so that the left side is always equal to or lesser!

    select a.Id L,b.id R
      into #cross
      from MyTable a
inner join mytable b on a.id <= b.id

Upvotes: 8

Related Questions