CASE WHEN Statement in the Filter

Question

For example there are two tables t1 and t2:

t1 as, the only value in the table is either 'Prime', 'Non-Prime' or 'All'. So it's something like this below, shipment_type can be one of the three values. It's 'Prime' in this example. 'All' means it can be either Prime or Non-Prime.

   shipment_type 
   Prime

and t2 as:

order_day   customer_id shipment_type   order_total
2/1/2024    118XXXXXXX  Prime   181.9
1/24/2024   118XXXXXXX  Prime   71.49
1/29/2024   118XXXXXXX  Non-Prime   814.38
1/29/2024   118XXXXXXX  Non-Prime   814.38

I want to filter table t2 based on the value in the t1 table but I don't know how to select all of the values in t2 table if shipment_type in the table t1 is 'All'.

I tried to filter table t2 like this, however this only includes Prime or Non-Prime results. I want to obtain all of the values in the t2 table if t1.shipment_type = 'All'

SELECT t1.* FROM t2
    WHERE
    t2.shipment_type = (SELECT CASE WHEN t1.shipment_type = 'Prime' THEN 'Prime' 
    WHEN t1.shipment_type = 'Non-Prime' THEN 'Non-Prime' 
    END AS customer_type 
    FROM t1)

I don't think adding the third condition in case when will help since there is no 'All' field in table t2.

Thanks and appreciating your response

Answer 1

There's no need to use the CASE clause. Simply use an INNER JOIN.

The condtion OR t1.shipment_type = 'All' ensures that all records from t2 are retrieved if t1.shipment_type = 'All' :

SELECT t2.*
FROM t2
INNER JOIN t1 ON t1.shipment_type = t2.shipment_type OR t1.shipment_type = 'All';

Demo here