Query Results

Question

I have a table: UserA, UserB, numberOfConnections

I would like to write a query that returns to me only rows that do not have the opposite, which I mean or an example: for data:

1 2 10
1 3 10
1 5 10
1 6 10
2 6 10
2 5 10
5 1 10
5 2 10
3 1 10

he must return

1 2 10
1 3 10
1 5 10
1 6 10
2 6 10
2 5 10

line:

5 1 10
5 2 10
3 1 10

arent valid because there are already matching

1 5 10
2 5 10
3 1 10

thanks for the help date

Answer 1

It will do what you want

SELECT mt1.UserA, mt1.UserB, mt1.numberOfConnections
FROM MyTable mt1
LEFT OUTER JOIN MyTable mt2 ON mt1.UserA = mt2.UserB
        AND mt1.UserB = mt2.UserA
WHERE mt2.UserA IS NULL
    OR mt1.UserA < mt2.UserA

Answer 2

Why not add UserA <UserB to the where section.

Answer 3

with t1 as (
select col1 ,col2,
     row_number() 
      over(partition by case when col1>col2 then col1+col2 else col2+col1 end 
           order by case when col1>col2 then col1+col2 else col2+col1 end) as rownum 
from table_1 )

select * from t1 where rownum =1

col1 col2 varchar, cast them

row_number group by

Answer 4

A- No Reverse

Select UserA, UserB, numberOfConnections
From TName
Where ID Not IN(
SELECT t1.ID
FROM TName As t1 INNER JOIN TName As t2 ON (t1.UserA = t2.UserB) AND (t1.UserB = t2.UserA))

B-

Select UserA, UserB, numberOfConnections
From TName
Where ID IN(
SELECT t1.ID
FROM TName As t1 INNER JOIN TName As t2 ON (t1.UserA = t2.UserB) AND (t1.UserB = t2.UserA))

C-

Select UserA, UserB, numberOfConnections
From TName
Where ID IN(
SELECT t1.ID
FROM TName As t1 INNER JOIN TName As t2 ON (t1.UserA = t2.UserB) AND (t1.UserB = t2.UserA) and (t1.UserA<t1.UserB))

D-

and union C