Question 112726 in Mysql Question posted in
The official MySQL documentation can be found here.

Select statement with conditions depending on the value of another column – same table (mysql)

I have table with 3 columns:

ID, 
Cancellation_Policy_Type 
Cancellation_Policy_Hours.

The query I would like to get to will allow me to select:

  • the min Cancellation_Policy_Hours which correspond to the Free Cancellation (if exists)
  • if the above doesn’t exist for the specific ID, then I want to check if there is a partially refundable
  • if none of the above exist, then check if there is No Refundable.

The below query is not correct but it may give a better idea about what I am trying to achieve:

IF (SELECT ID, Cancellation_Policy_Type, MIN(Cancellation_Policy_Hours) from MYTABLE WHERE Cancellation_Policy_Type = 'Free Cancellation') IS NOT NULL)
THEN (SELECT ID, Cancellation_Policy_Type, MIN(Cancellation_Policy_Hours) from MYTABLE WHERE Cancellation_Policy_Type = 'Free Cancellation')
ELSEIF (SELECT ID, Cancellation_Policy_Type, MIN(Cancellation_Policy_Hours) from MYTABLE WHERE Cancellation_Policy_Type = 'Free Cancellation') IS NULL AND (SELECT ID, Cancellation_Policy_Type, MIN(Cancellation_Policy_Hours  from MYTABLE WHERE Cancellation_Policy_Type = 'Partially Refundable') IS NOT NULL Then (SELECT ID, Cancellation_Policy_Type, MIN(Cancellation_Policy_Hours) from MYTABLE WHERE Cancellation_Policy_Type = 'Partially Refundable')
ELSEIF (SELECT ID, Cancellation_Policy_Type, MIN(Cancellation_Policy_Hours) from MYTABLE WHERE Cancellation_Policy_Type = 'Free Cancellation') IS NULL AND (SELECT ID, Cancellation_Policy_Type, MIN(Cancellation_Policy_Hours) from MYTABLE WHERE Cancellation_Policy_Type = 'Partially Refundable') IS NULL  THEN (SELECT ID, Cancellation_Policy_Type, MIN(Cancellation_Policy_Hours) from MYTABLE WHERE Cancellation_Policy_Type = 'No Refundable') 
END

Below you will find an example of my dataset:

This is the table which contains all data regarding the cancellation policies of every single ID:

ID Cancellation_Policy_Type Cancellation_Policy_Hours
1 No Refundable 17520
1 Partially Refunable 168
1 Free Cancellation 96
2 No Refundable 17520
2 Partially Refunable 336
2 Free Cancellation 48
3 No Refundable 17520
3 Partially Refunable 336
4 No Refundable 17520

Below is the desired result, that is a table which contains other pieces of information (including production) and the 2 columns where for every single ID repeats the best available cancellation policy type and hours:

ID Most Flexible Cancellation Type Most Flexible Cancellation Hours Other Columns (including buckets)
1 Free Cancellation 96 a
1 Free Cancellation 96 b
1 Free Cancellation 96 c
2 Free Cancellation 48 a
2 Free Cancellation 48 b
2 Free Cancellation 48 c
3 Partially Refunable 336 a
3 Partially Refunable 336 b
3 Partially Refunable 336 c
4 No Refundable 17520 a
4 No Refundable 17520 b
4 No Refundable 17520 c 
SELECT
a.ID
, Most_Flexible_Policy_Type 
, Most_Flexible_Cancellation_Hours
, a.BookingWindowBuckets
FROM Production a
LEFT JOIN Property b on a.ID = b.ID
GROUP BY
1,2,3,4

Thank you

Tags:

2

Answers


  1. 0

    You have not given enough information to be able to help you with any degree of confidence that it is the "right" way but (guessing here) you could try –

    SELECT
    ID,
    MIN(IF(Cancellation_Policy_Type = 'Free Cancellation', Cancellation_Policy_Hours, NULL)) AS minFreeCancellation,
    MIN(IF(Cancellation_Policy_Type = 'Partially Refundable', Cancellation_Policy_Hours, NULL)) AS minPartiallyRefundable,
    MIN(IF(Cancellation_Policy_Type = 'No Refundable', Cancellation_Policy_Hours, NULL)) AS minNoRefundable
FROM MYTABLE
WHERE ID = ?
GROUP BY ID;

    If you provide an example in the form of full table structure (CREATE statement), some sample data, some stats and the desired outcome you are more likely to get the "right" answer. The above example of conditional aggregation is unlikely to be the best way to do it but it will probably provide what you are looking for. Depending on your dataset, just running the separate queries may be the best solution.

    UPDATE
    Here is one way of doing this using a window function (MySQL 8) –

    WITH Properties (ID, Cancellation_Policy_Type, Cancellation_Policy_Hours, Most_Flexible) AS (
    SELECT
        ID, Cancellation_Policy_Type, Cancellation_Policy_Hours,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY
            CASE Cancellation_Policy_Type
                WHEN 'Free Cancellation' THEN 0
                WHEN 'Partially Refundable' THEN 1
                WHEN 'No Refundable' THEN 2
            END ASC, Cancellation_Policy_Hours ASC
        )
    FROM Property
)
SELECT
    a.ID,
    b.Cancellation_Policy_Type,
    b.Cancellation_Policy_Hours,
    a.BookingWindowBuckets
FROM Production a
LEFT JOIN Properties b on a.ID = b.ID
WHERE b.Most_Flexible = 1;
    Login or Signup to reply.
  2. 0

    I understand that, for each row in production, you want to bring from table property the cancellation policy with the least policy hours.

    We can do this with window functions to rank the policies of feach id, and a join to production:

    select d.id, 
    p.cancellation_type_policy  most_flexible_cancellation_type,
    p.cancellation_policy_hours most_flexible_cancellation_hours
from production d
inner join (
    select p.*, row_number() over(partition by id order by cancellation_policy_hours) rn
    from property p
) p on p.id = d.id
where rn = 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search