SQL – Select rows partially depending on another table

I am very new to the database and queries. I am not sure if this is very basic. Please help me to find a solution.

I have two tables – subscription and customer with primary keys as subscription_id and customer_id respectively. the customer_id is foreign key pointing to customer table customer_id column. Below is a sample of table and its data:

subscription

subscription_id  customer_id   subscription_start_date   subscription_end_date  subscription_status 
1001             1             1-JAN-2020                31-DEC-2020              PENDING       
1002             2             1-JUN-2020                31-MAY-2021              PENDING       
1003             3             4-JUL-2020                3-JUL-2021               ARCHIVED  
1004             4             2-APR-2020                1-APR-2021               PENDING           
1005             5             3-APR-2020                2-APR-2021               ARCHIVED      
1006             6             21-JAN-2020               20-JAN-2021              PENDING   
1007             7             22-JAN-2020               21-JAN-2021              PENDING   

Customer

customer_id membership_type  membership_start_date   membership_status 
1            GOLD             1-JAN-2020             ACTIVE
2            PLATINUM         1-JUN-2020             ACTIVE
3            PLATINUM         5-JUL-2020             PROCESSING
4            GOLD             2-APR-2020             PROCESSING
5            GOLD             3-APR-2020             ACTIVE
6            GOLD             21-JAN-2020            PROCESSING
7            GOLD             22-JAN-2019            EXPIRED

I want to query all the subscriptions which satisfies following two criteria

1. subscription_satus is pending and membership_type is GOLD or PLATINUM and membership_status is ACTIVE.(1001,1002)
2. subscription_status is archived only when customer membership_type is PLATINUM and the membership_start_date is between subscription_start_date and subscription_end_date and membership_status is PROCESING(1003)

So we should get 1001, 1002 records under criteria 1. and 1003 under criteria 2

Answer

Query should look like this; lines #1 – 21 represent sample data, you don’t type that. Query you do need begins at line #22 and contains two parts of the WHERE clause: the first satisfies the first condition, while the second satisfies the second condition.

Note that sample MEMBERSHIP_START_DATE for curstomer 3 is 02-JUL-2020 which is not between subscription start (4-JUL-2020) and end (3-JUL-2021) dates, so – 1003 isn’t part of the result set. Modify membership start date to e.g. 22-JUL-2020 and it will be.

SQL> with
  2  subscription (subscription_id, customer_id, subscription_start_date,
  3    subscription_end_date, subscription_status) as
  4    (select 1001, 1, date '2020-01-01', date '2020-12-31', 'PENDING'  from dual union all
  5     select 1002, 2, date '2020-06-01', date '2021-05-31', 'PENDING'  from dual union all
  6     select 1003, 3, date '2020-07-04', date '2021-07-03', 'ARCHIVED' from dual union all
  7     select 1004, 4, date '2020-04-02', date '2021-04-01', 'PENDING'  from dual union all
  8     select 1005, 5, date '2020-04-03', date '2021-04-02', 'ARCHIVED' from dual union all
  9     select 1006, 6, date '2020-01-21', date '2021-01-20', 'PENDING'  from dual union all
 10     select 1007, 7, date '2020-01-22', date '2021-01-21', 'PENDING'  from dual
 11    ),
 12  customer (customer_id, membership_type, membership_start_date,
 13    membership_status) as
 14    (select 1, 'GOLD'    , date '2020-01-01', 'ACTIVE'     from dual union all
 15     select 2, 'PLATINUM', date '2020-06-01', 'ACTIVE'     from dual union all
 16     select 3, 'PLATINUM', date '2020-07-02', 'PROCESSING' from dual union all
 17     select 4, 'GOLD'    , date '2020-04-02', 'PROCESSING' from dual union all
 18     select 5, 'GOLD'    , date '2020-04-03', 'ACTIVE'     from dual union all
 19     select 6, 'GOLD'    , date '2020-01-21', 'PROCESSING' from dual union all
 20     select 7, 'GOLD'    , date '2019-01-22', 'EXPIRED'    from dual
 21    )

---

 22  select s.subscription_id
 23  from subscription s join customer c on c.customer_id = s.customer_id
 24  where (    s.subscription_status = 'PENDING'
 25         and c.membership_type in ('GOLD', 'PLATINUM')
 26         and c.membership_status = 'ACTIVE'
 27        )
 28     or
 29       (     s.subscription_status = 'ARCHIVED'
 30         and c.membership_type = 'PLATINUM'
 31         and c.membership_start_date between s.subscription_start_date
 32                                         and s.subscription_end_date
 33         and c.membership_status = 'PROCESSING'
 34       )
 35  /

SUBSCRIPTION_ID
---------------
           1001
           1002

SQL>