SQLite – Get all other rows with any duplicate column value

sqlite

I have a table of users, with information about them and the combination of the fields and UserName is unique (A row as a whole, not only on one field)

Users

+----------+--------+--------+--------+
| UserName | Field1 | Field2 | Field3 |
+----------+--------+--------+--------+
| UserNum1 | AAABBB | CCCDDD | EEEFFF |
| UserNum2 | AAABBB | IIIJJJ | KKKFFF |
| UserNum3 | LLLMMM | CCCDDD | PPPQQQ |
| UserNum4 | RRRSSS | TTTUUU | VVVWWW |
+----------+--------+--------+--------+

Given one column value to find, I have to find other users with ANY same column values for all columns dynamically.

For example, I only provided the username "UserNum1" as UserName, It should return User 2 and 3 because of the values of their Field1 and Field2 respectively.

Desired output:

+----------+--------+--------+--------+
| UserName | Field1 | Field2 | Field3 |
+----------+--------+--------+--------+
| UserNum2 | AAABBB | IIIJJJ | KKKFFF | # Field1 has same value on User1 & User2
| UserNum3 | LLLMMM | CCCDDD | PPPQQQ | # Field2 has same value on User1 & User3
+----------+--------+--------+--------+

Is there ways to do this in one query? Or do I really have to query each column and do the other processing in my backend

FYI: The initial problem is, finding possible user duplicate accounts or "alts" based on similar fields

Best Answer

sqlite> select
   ...>    Username,
   ...>    Field1,
   ...>    Field2,
   ...>    Field3,
   ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field1=u1.field1)  as F1check,           ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field2=u1.field2)  as F2check,           ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field3=u1.field3)  as F2check
   ...> from User u1;
Username    field1      field2      field3      F1check     F2check     F2check
----------  ----------  ----------  ----------  ----------  ----------  ----------
UserNum1    AAABBB      CCCDDD      EEEFFF      UserNum2    UserNum3
UserNum2    AAABBB      IIIJJJ      KKKFFF      UserNum1
UserNum3    LLLMMM      CCCDDD      PPPQQQ                  UserNum1
UserNum4    RRRSSS      TTTUUU      VVVWWW
sqlite>

NOTE: if you have more values that are double, only the first one is show, i.e. if you have also this record:

insert into User values ('Usernum5','AAABBB','XXXYYY','XXXYYY')

then the output will show:

sqlite> select
   ...>    Username,
   ...>    Field1,
   ...>    Field2,
   ...>    Field3,
   ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field1=u1.field1)  as F1check,           ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field2=u1.field2)  as F2check,           ...>    (select u2.Username from User u2 where u2.Username<>u1.Username and u2.Field3=u1.field3)  as F2check
   ...> from User u1;
Username    field1      field2      field3      F1check     F2check     F2check
----------  ----------  ----------  ----------  ----------  ----------  ----------
UserNum1    AAABBB      CCCDDD      EEEFFF      UserNum2    UserNum3
UserNum2    AAABBB      IIIJJJ      KKKFFF      UserNum1
UserNum3    LLLMMM      CCCDDD      PPPQQQ                  UserNum1
UserNum4    RRRSSS      TTTUUU      VVVWWW
Usernum5    AAABBB      XXXYYY      XXXYYY      UserNum1
sqlite>

Where you see (on the line 'Usernum5') only a reference to Usernum1, and not to Usernum2.

Related Question