KItis KItis - 5 months ago 10
MySQL Question

How to avoid duplicates in following SQL scenario

I have a table called LIKES as follows.

enter image description here

As you can see it is having two columns. UserName1, UserName2.
What this table contains is that, If one person follow other persons facebook page etc.

For example, If Jon follow bobs page then there is a entry in the table as Jon, bob, If bob follows Jon facebook page, then there is a entry called Bob, Jon.

So I want to find out all the users who are following each others profile and I want it without duplicates.

I have following query, which give results of finding users who follow each others profile. but I am not able to remove duplicates

SELECT L1.USERNAME1, L2.USERNAME2
FROM LIKES L1,
LIKES L2
WHERE L1.USERNAME1=L2.USERNAME2
AND L1.USERNAME2=L2.USERNAME1


Final output from the given table should be Jon Bob, or Bob , Jon, not the both.

my query gives the both results, How can I remove the duplicates in the resluts

Answer

First, don't use comma-style joins. That syntax has been outdated for a long time. Second, one way you can avoid duplicates in this case is to require that the first name you report in your result set occur before the first alphabetically. You can do this safely because any pair of names that will appear in your result set must appear in the source table in both orders (e.g. ("Bob", "Jon") and ("Jon", "Bob")). I am assuming here that you don't need to deal with the case of a user who follows his own page. For instance:

select * 
from likes L1 
where 
    L1.username1 < L1.username2 and
    exists (select 1 from likes L2 where L1.username1 = L2.username2 and L1.username2 = L2.username1);

Result:

username1 username2
Bob       Jon

Click here for a SQL fiddle that demonstrates this approach using your sample data.