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!
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.
An alternative query could be:
select c.compare1 >= p.compare1
and c.compare2 >= p.compare2
and c.compare3 >= p.compare3
and c.compare4 >= p.compare4
and c.compare5 >= p.compare5
as res
from mytable p
, mytable c
where p.username = 'Adam'
and c.username = 'Charlie';