Geek Answers Handbook

How to list multiple field matches from one table in oracle SQL

I have the following table:

create table Likes(ID1 number(5), ID2 number(5));
insert into Likes values(1689, 1709);
insert into Likes values(1709, 1689);
insert into Likes values(1782, 1709);
insert into Likes values(1911, 1247);
insert into Likes values(1247, 1468);
insert into Likes values(1641, 1468);
insert into Likes values(1316, 1304);
insert into Likes values(1501, 1934);
insert into Likes values(1934, 1501);
insert into Likes values(1025, 1101);

The table shows the "liked" users identified by their identifiers. Liking is a one-way connection (if ID1 likes ID2, this does not mean that TD2 likes ID1).

I want to find those IDs where there is a two-way connection (where the "liker" is "liked" by the user he loves).

I am starting with Oracle SQL, I hope my question is not that corny ...

+4
source share
4 answers

You can do this with a connection:

SELECT t.id1,t.id2
FROM Likes t
INNER JOIN Likes s
 ON(t.id1 = s.id2 and t.id2 = s.id1)

Or with EXISTS ()

SELECT t.*
FROM Likes t
WHERE EXISTS(select 1 FROM Likes s
             WHERE t.id1 = s.id2
                 AND t.id2 = s.id1)
+4
source

id1 = X, id2 = Y , , id1 = Y, id2 = X.
SQL EXISTS :

SELECT * 
FROM likes t
WHERE EXISTS (
   SELECT 1 FROM likes t1
   WHERE t.id1 = t1.id2 AND t.id2 = t1.id1
)
+3

try the following:

with tab as (select id1, id2 from Likes)
select id1, id2 from tab
intersect
select id2, id1 from tab;

It should work better because it will Likesonly read the table once

+3
source

Use this query:

     SELECT
            id1, id2 from Likes L1
        INNER JOIN (
          Select id1, id2 from Likes
        ) as L2
        on (L1.id1 = L2.id)
WHERE L1.id2 = L2.id1
+3
source

More articles:


All Articles