SQL Where condition with 2 contradicting clauses Oracle-CodePudding

I have a table with a list of elements, These elements have a "creator type" determined by one relation. This relation field value can be 1-2-3.

I have to make a sql query with the next conditions:

Get all elements with value 1 and (2 or 3)
Ignore elements with value 1 without 2 or 3.

SCHEDULES

ID|NAMEUNIQUE|STARTTIME|ENDTIME|STATUS|CREATOR|CREATOR_TYPE
1 |AAAAAAAAAA|.........|.......|......|.......|1
2 |BBBBBBBBBB|.........|.......|......|.......|2
3 |BBBBBBBBBB|.........|.......|......|.......|1
4 |CCCCCCCCCC|.........|.......|......|.......|3
5 |DDDDDDDDDD|.........|.......|......|.......|1

In this case, the query should return rows with ID 2,3,4,5 and discard 1 because is an unique schedule (AAAAAAAAAA) that not have creator type 2 or 3.

Sorry for my englisht, it's hard try to explan this to me

CodePudding user response：

You can use conditional aggregation in analytic functions:

SELECT *
FROM   (
  SELECT s.*,
         COUNT(CASE creator_type WHEN 1 THEN 1 END)
           OVER (PARTITION BY nameunique) AS num_1s,
         COUNT(CASE creator_type WHEN 2 THEN 1 END)
           OVER (PARTITION BY nameunique) AS num_2s,
         COUNT(CASE creator_type WHEN 3 THEN 1 END)
           OVER (PARTITION BY nameunique) AS num_3s
  FROM   schedules s
)
WHERE num_1s > 0
AND   (num_2s > 0 OR num_3s > 0);

Which, for the sample data:

CREATE TABLE schedules (id, nameunique, creator_type) AS
SELECT 1, 'AAAAA', 1 FROM DUAL UNION ALL
SELECT 2, 'BBBBB', 2 FROM DUAL UNION ALL
SELECT 3, 'BBBBB', 1 FROM DUAL UNION ALL
SELECT 4, 'CCCCC', 1 FROM DUAL UNION ALL
SELECT 5, 'CCCCC', 3 FROM DUAL UNION ALL
SELECT 6, 'DDDDD', 1 FROM DUAL;

Outputs:

ID NAMEUNIQUE CREATOR_TYPE NUM_1S NUM_2S NUM_3S

2 BBBBB 2 1 1 0

3 BBBBB 1 1 1 0

4 CCCCC 1 1 0 1

5 CCCCC 3 1 0 1

ID	NAMEUNIQUE	CREATOR_TYPE	NUM_1S	NUM_2S	NUM_3S
2	BBBBB	2	1	1	0
3	BBBBB	1	1	1	0
4	CCCCC	1	1	0	1
5	CCCCC	3	1	0	1

db<>fiddle here

CodePudding user response：

select *
from   (
         select s.*, 
                min(creator_type) over (partition by nameunique) as min_type,
                max(creator_type) over (partition by nameunique) as max_type
         from   schedules s
       )
where  min_type = 1 and max_type > 1
;