MySQL 8.0

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') )


SERIAL
DISPLAY
REMARK
A
B
C


2
YES
ITEM
APPLE
BALLON
CATTLE

5
YES
COLOR
APPLE
BALL
CAT

SERIAL	DISPLAY	REMARK	A	B	C
1	YES	ITEM	APPLE	BATTLE	CAT
3	YES	ITEM	AROPLANE	BALL	CANDLE
7	YES	ITEM	APPLE	BALL	CAT