比较同一表中2行之间的所有列

Question

I'm looking to compare all columns between two rows in a table using php/mysqli but just can't figure out how to do it. My table looks something like:

+----------+----------+----------+----------+----------+--------------+
| username | compare1 | compare2 | compare3 | compare4 | compare5 etc |
+----------+----------+----------+----------+----------+--------------+
| Adam     |        1 |        0 |        1 |        1 |            0 |
| Billy    |        1 |        1 |        1 |        1 |            0 |
| Charlie  |        1 |        0 |        0 |        1 |            1 |
+----------+----------+----------+----------+----------+--------------+

I want to select say username Charlie as the child and username Adam as the parent then compare their values for all other columns (there's quite a few) in the table. If any of the child values is 0 where the parent value is 1 then the query returns false, otherwise, it returns true.

Thanks in advance!

Answer 1

You can do:

select (count(*) = 0) as flag
from t tp join
     t tc
     on tp.username = 'Adam' and tc.username = 'Charlie'
where (0, 1) in ( (tp.compare1, tc.compare1), (tp.compare2, tc.compare2),
                  . . .
                );

Note: This assumes that each username has only one row.