5

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

Felice Pollano
  • 32,832
  • 9
  • 75
  • 115

1 Answers1

8

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
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • To get the correct result, a.id should be less than b.id, and the equal sign should not be used in the join condition. – QMaster Oct 24 '21 at 21:23