By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.36 |
CREATE TABLE `table1` (
`SERIAL` bigint(20) UNSIGNED NOT NULL,
`DISPLAY` varchar(20) DEFAULT NULL,
`REMARK` varchar(20) DEFAULT NULL,
`A` varchar(20) DEFAULT NULL,
`B` varchar(20) DEFAULT NULL,
`C` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ALTER TABLE `table1`
ADD PRIMARY KEY (`SERIAL`),
ADD UNIQUE KEY `SERIAL` (`SERIAL`);
ALTER TABLE `table1`
MODIFY `SERIAL` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
INSERT INTO `table1` (`SERIAL`, `DISPLAY`, `REMARK`, `A`, `B`, `C`) VALUES
(1, 'YES', 'ITEM', 'APPLE', 'BATTLE', 'CAT'),
(2, 'YES', 'ITEM', 'APPLE', 'BALLON', 'CATTLE'),
(3, 'YES', 'ITEM', 'AROPLANE', 'BALL', 'CANDLE'),
(4, 'NO', 'ITEM', 'APPLE', 'BALL', 'CAT'),
(5, 'YES', 'COLOR', 'APPLE', 'BALL', 'CAT'),
(6, 'YES', 'ITEM', NULL, NULL, NULL),
(7, 'YES', 'ITEM', 'APPLE', 'BALL', 'CAT');
Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 1
Records: 7 Duplicates: 0 Warnings: 0
SELECT SERIAL,DISPLAY, REMARK,A,B,C
FROM table1
WHERE DISPLAY="YES"
AND REMARK ="ITEM" AND (
A in('APPLE')
AND C in('CAT')
OR B in('BALL')
)
| SERIAL | DISPLAY | REMARK | A | B | C |
|---|---|---|---|---|---|
| 1 | YES | ITEM | APPLE | BATTLE | CAT |
| 3 | YES | ITEM | AROPLANE | BALL | CANDLE |
| 7 | YES | ITEM | APPLE | BALL | CAT |
SELECT SERIAL,DISPLAY, REMARK,A,B,C
FROM table1
WHERE DISPLAY="YES"
-- AND REMARK ="ITEM" AND (A in('APPLE') AND C in('CAT') OR B in('BALL') )
-- AND REMARK !="ITEM" OR NOT(A in('APPLE') AND C in('CAT') OR B in('BALL') )
AND REMARK ="ITEM" AND NOT((A in('APPLE') AND A IS NOT NULL)
AND (C in('CAT') AND C IS NOT NULL)
OR (B in('BALL') AND B IS NOT NULL) )
| SERIAL | DISPLAY | REMARK | A | B | C |
|---|---|---|---|---|---|
| 2 | YES | ITEM | APPLE | BALLON | CATTLE |
| 6 | YES | ITEM | null | null | null |
SELECT SERIAL,DISPLAY, REMARK,A,B,C
FROM table1
WHERE DISPLAY="YES"
AND REMARK ="ITEM" AND ( (NOT((A in('APPLE') AND A IS NOT NULL))
OR NOT(C in('CAT') AND C IS NOT NULL))
AND NOT(B in('BALL') AND B IS NOT NULL) )
| SERIAL | DISPLAY | REMARK | A | B | C |
|---|---|---|---|---|---|
| 2 | YES | ITEM | APPLE | BALLON | CATTLE |
| 6 | YES | ITEM | null | null | null |
SELECT SERIAL,DISPLAY, REMARK,A,B,C
FROM table1
WHERE DISPLAY="YES"
AND REMARK ="ITEM" AND ( A NOT in('APPLE') OR A IS NULL
OR C NOT in('CAT') OR C IS NULL)
AND (B NOT in('BALL') OR B IS NULL)
| SERIAL | DISPLAY | REMARK | A | B | C |
|---|---|---|---|---|---|
| 2 | YES | ITEM | APPLE | BALLON | CATTLE |
| 6 | YES | ITEM | null | null | null |