SQL gruoup by special condition and filter-CodePudding

I have this data:

Blockquote

id_doc	id_exp	id_act	fecha
500	2000	3	2021-10-23
501	2000	4	NULL
502	2000	6	2021-10-23
503	2000	2	2021-10-23
504	3000	4	NULL
505	3000	6	2021-10-23
506	4000	3	2021-10-23
507	4000	4	NULL

Blockquote

I want show like this (only show id_act=4 and fecha is null) but show the same id_exp if id_act not in (2, 6):

Blockquote

id_doc	id_exp	id_act	fecha
506	4000	3	2021-10-23
507	4000	4	NULL

CodePudding user response：

You can chekc WITH NOT EXISTS if one id_exp has a id_act in (2,6)

SELECT `id_doc`, `id_exp`, `id_act`, `fecha` FROM tab1
  WHERE `id_exp` IN 
(SELECT `id_exp`  FROM tab1 t1 WHERE 
     `id_act` = 4 and `fecha` IS  NULL
AND NOT EXISTS ( SELECT 1 FROM tab1 WHERE `id_act` IN (2,6) AND `id_exp` = t1.`id_exp` ))

id_doc	id_exp	id_act	fecha
506	4000	3	2021-10-23
507	4000	4	null

fiddle