I have a table called LIKES as follows.
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,
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);
username1 username2 Bob Jon
Click here for a SQL fiddle that demonstrates this approach using your sample data.