Simple SQL query on one single table

Question

I'm ashamed but I must ask this SQL question. I'm actually using SQLITE on Android.

Let's assume there is only one table with 2 columns.

USER1 USER2 
111   555
111   300
445   111
555   111
325   111
111   233
300   111

I'd like to get the following results:

USER1 USER2
111   300
111   555

I tried INNER JOIN and INTERSECT but I could not get it to work.

Thanks a lot!!!

Answer 1

Looks like you want rows where the switched user1/user2 also exist in the table. And also user1 < user2. Do a self join:

select t1.user1, t1.user2
from table t1
join table t2 on t1.user1 = t2.user2 and t1.user2 = t2.user1
where t1.user1 <= t1.user2