Exclude ALL rows where the ids are the same, BUT one of the rows with the same ids meets the criteria and other doesn’t

How do I filter out ALL rows where the ids are the same, BUT one of the rows with the same ids meets the criteria and other doesn’t?

create table people_table
        (
          ids varchar(30),
          id_code varchar(30),
          num_1 varchar(30)
        );

        insert into people_table values ('1','ABC','80');
        insert into people_table values ('2','ABC','80');
        insert into people_table values ('2','ZYX','81');
        insert into people_table values ('4','ABC','80');
        insert into people_table values ('4','DEF','80');
        insert into people_table values ('5','ABC','80');
        insert into people_table values ('6','ZYX','');

Current Code:

        SELECT people_table.*
FROM people_table
WHERE ids IN (SELECT ids
             FROM people_table
             WHERE (id_code between '123' AND '567')
                    AND ((num_1 = '80')
                    OR (num_1 = '' ))
             GROUP BY ids
             HAVING COUNT(ids)>=1)
AND id_code between '123' AND '567';

Answer

You can try to use aggregate window function, writing your condition which you want to exclude. then filter them.

Query 1:

SELECT ids,id_code,num_1,num_2
FROM (
  SELECT *,
        MAX(CASE WHEN id_code < '99201' OR id_code > '99499' 
                  AND ((num_1 = '25' OR num_2 = '25')
                    OR (num_1 = '25' OR num_2 = '')
                    OR (num_1 = '' OR num_2 = '25')) THEN 1 END) 
           OVER (PARTITION BY ids order by ids) exceptFlag
  FROM people_table 
) t1
WHERE exceptFlag IS NULL

Results:

| ids | id_code | num_1 | num_2 |
|-----|---------|-------|-------|
|   1 |   99213 |    25 |       |
|   3 |   99213 |    25 |       |
|   3 |   99201 |       |       |
|   4 |   99213 |    25 |       |
|   4 |   99201 |    25 |       |
|   5 |   99213 |    25 |       |

If your column id_code stored number value, I would suggest you use number type.