How do can I do this?

Hi,

I have this table:

colA ; colB

1) 1 2

2) 2 1

3) 2 6

4) 3 6

5) 6 3

6) 6 7

7) 7 6

and I need to select from it only the rows that do not have an opposite pair, i.e. only line (3) as it is 2 - 6 and there is no line 6 - 2, all the other lines have opposites, like line (1) 1 - 2 has line (2) 2 - 1 and line (4) has line (5), etc.

any ideas?

thanks.

SELECT b.id, b.colA, b.colB FROM mytest b WHERE b.id NOT IN (SELECT a.id

FROM myTest a INNER JOIN (SELECT id, colA, colB

FROM myTest ) d ON a.colA=d.colB AND a.colB=d.colA)

|||

thanks a lot,

but I have only colA and colB, the "x)" in my post was only for being more clear about it.

|||

Maybe something like this:

declare @.mock table (colA int, colB int)
insert into @.mock values (1, 2)
insert into @.mock values (2, 1)
insert into @.mock values (2, 6)
insert into @.mock values (3, 6)
insert into @.Mock values (6, 3)
insert into @.mock values (6, 7)
insert into @.mock values (7, 6)

select *
from @.mock x
where not exists
( select 0 from @.mock y
where x.cola = y.colb
and x.colb = y.cola
)

-- - Output: -

-- colA colB
-- -- --
-- 2 6

|||

Hi,

thanks to all but here is the solution:

SELECT *

FROM (SELECT * FROM tbl) AS t3

EXCEPT

SELECT *

FROM (SELECT t1.*

FROM tbl AS t1 INNER JOIN

tbl AS t2

on t1.colA = t2.colB AND

t1.colB = t2.colA) AS t4

better to know the new operators... :)

|||

Thank you, Alan; you caught me. I have missed the EXCEPT join at least once before, too. Please keep after me until I get it right. :-)

Dave

|||Using NOT EXISTS is the easiest way to write the query. This version using EXCEPT and joins will be much slower.