0

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

0
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.