select row that column2 not equal to column1

Question

I need help with a SQL Query

I have this table

likes
 friend    friend2
      1          2
      2          1
      3          1
      4          5

I need to select only one pair of the friends that like each other.

With my current select I'm obtaining this

 id     name         id     name
1709    Cassandra    1689   Gabriel 
1689    Gabriel      1709   Cassandra
1501    Jessica      1934   Kyle    
1934    Kyle         1501   Jessica

but the expected query result is:

 id     name        id     name
1709    Cassandra   1689    Gabriel 
1501    Jessica     1934    Kyle

Which database and version are you using? – Mark Byers Nov 07 '11 at 01:11 — Mark Byers, Nov 07 '11 at 01:11

score 1 · Accepted Answer · answered Nov 07 '11 at 01:07

If you are sure that every pair is entered in the database twice (once as A, B and again as B,A) then you can use this:

 SELECT friend, friend2
 FROM yourtable
 WHERE friend < friend2

If there can be some pairs that are only entered once, then you can use this:

 SELECT DISTINCT
     LEAST(friend, friend2) AS friend,
     GREATEST(friend, friend2) AS friend2
 FROM yourtable

score 1 · Answer 2 · 2011-11-07T01:16:39.287

Try something like this:

select *
from
(
    select
        case
            when id1 > id2 then id2
            when id1 < id2 then id1
        end as Friend1,
        case
            when id1 < id2 then id2
            when id1 > id2 then id1
        end as Friend2
    from TestMatching
) a
group by Friend1, Friend2

What this does here is it uses a subquery and the subquery is just a couple of CASE blocks to order the friendship. And then it does a group by to create the summary of friendships. If you wanted to see what friends are BOTH ways, then you'd do something like this:

select *
from
(
    select
        case
            when id1 > id2 then id2
            when id1 < id2 then id1
        end as Friend1,
        case
            when id1 < id2 then id2
            when id1 > id2 then id1
        end as Friend2
    from TestMatching
) a
group by Friend1, Friend2
having COUNT(*) > 1    -- this is the added clause to get consenting friends

The result query is all the friends that are friends with each other.

select row that column2 not equal to column1

2 Answers2