I have the following table:
| ID | SPNR | SP1 | SP2 | SP2 |
|---|---|---|---|---|
| 01 | 2 | x | x | |
| 02 | 2 | x | ||
| 03 | 3 | x | x | |
| 04 | 2 | x | ||
| 05 | 3 | x | x | x |
SPNR is the amount of SP needed.
The first record is ok. In the second you need 2 but there is only one. In the third, 3 are needed and there are only two.
I need a query that lists the records that do not meet the condition (ID = 2 ,3, 4)
I am relatively new to MYSQL and cannot find the "count if" option for the same record. Can someone shed some light on me.
CodePudding user response：
You could use a boolean sum expression here:
SELECT *
FROM yourTable
WHERE (SP1 IS NULL) (SP2 IS NULL) (SP3 IS NULL) != SPNR;