MySQL Multiple Case When Exists Statement

Question

I have two tables. Let's call it: SEATS and SEAT_ALLOCATION_RULE table.

Below are the table schema:

CREATE TABLE IF NOT EXISTS `SEATS` (
  `SeatID` int(11) NOT NULL AUTO_INCREMENT,
  `SeatName` varchar(255) NOT NULL DEFAULT '',
   PRIMARY KEY (`SeatID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

INSERT INTO `SEATS` (`SeatID`, `SeatName`) VALUES
(1, 'Super VIP'),
(2, 'VIP'),
(3, 'Business'),
(4, 'Economy'),
(5, 'Standing');

CREATE TABLE IF NOT EXISTS `SEAT_ALLOCATION_RULE` (
 `SeatID` int(11) NOT NULL DEFAULT '0',
 `Origin` varchar(50) NOT NULL DEFAULT '0',
 `Destination` varchar(50) NOT NULL DEFAULT '',
 `Passenger_Type` varchar(25) NOT NULL DEFAULT '',
  PRIMARY KEY (`SeatID`,`Origin`,`Destination`,`Passenger_Type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `SEAT_ALLOCATION_RULE` (`SeatID`, `Origin`, `Destination, `Passenger_Type`) VALUES
(1, 'Malaysia','',''),
(2, 'Malaysia','Singapore',''),
(3, 'Malaysia','Singapore','Senior_Citizen'),
(4, 'Bangkok','Japan','Student'),
(5, 'Cambodia','China','Senior_Citizen');

SEAT_ALLOCATION_RULE table determines which seat should a passenger be assigned to based on the following order in priority:

1. Origin, destination, and  passenger_type match
2. Origin and destination match
3. Origin match

It means that if all the fields (origin, destination, and passenger_type) match, it should take higher priority than if it is just two fields match and so on. If a column is empty, it is considered as unspecified and hence has lower priority. So, for example:

• If the Origin is Malaysia, Destination is Singapore, and Passenger_Type is Senior_Citizen, it should return seatID 3
• If the Origin is Malaysia, Destination is Singapore, and Passenger_Type is Student, it should return seatID 2 (since it only match Origin and Destination)
• If the Origin is Malaysia, Destination is US, and Passenger_Type is Student, it should return seatID 1 (since it only match Origin).

Now, based on the rules above, if the origin is Malaysia, destination is Singapore, and Passenger_Type is student, the query to return seatID is as follow:

SELECT s.SeatID, s.SeatName
FROM SEATS s
WHERE
CASE WHEN EXISTS(
  select 1
  from SEAT_ALLOCATION_RULE r
  where s.SeatID = r.SeatID
  AND r.Origin = 'Malaysia'
  AND r.Destination = 'Singapore'
  AND r.Passenger_Type='Student') Then 1
WHEN EXISTS(
  select 1
  from SEAT_ALLOCATION_RULE r
  where s.SeatID = r.SeatID
  AND r.Origin = 'Malaysia'
  AND r.Destination = 'Singapore'
  AND r.Passenger_Type='') Then 1
WHEN EXISTS(
  select 1
  from SEAT_ALLOCATION_RULE r
  where s.SeatID = r.SeatID
  AND r.Origin = 'Malaysia'
  AND r.Destination = ''
  AND r.Passenger_Type='') Then 1 END

However, the query above does not work as it will return seatID 1 and 2, but the expected output is only seatID 2 (since origin and destination matches and it takes higher precedence). Can someone help to correct my SQL query?

Answer 1

This should do the trick:

select seatid
  from seat_allocation_rule sar
order by ((sar.origin = :origin) << 2) + ((sar.destination = :destination) << 1) + (sar.passenger_type = :passenger_type) desc,
         ((sar.origin <> '')     << 2) + ((sar.destination <> '')          << 1) + (sar.passenger_type <> '')             asc
 limit 1

To understand how:

create table testcase (
    origin         varchar(255),
    destination    varchar(255),
    passenger_type varchar(255),
    expected_seat  int(11)
);

insert into testcase values ('Malaysia','Singapore','Senior_Citizen',3),
                            ('Malaysia','Singapore','Student',2),
                            ('Malaysia','US','Student',1);

select * from (
select tc.*,
       sar.seatid,
       case when sar.seatid = tc.expected_seat then 'Y' else '-' end as pass,
         ((sar.origin = tc.origin) << 2)
       + ((sar.destination = tc.destination) << 1)
       + ((sar.passenger_type = tc.passenger_type) << 0) as score,
         ((sar.origin <> '') << 2)
       + ((sar.destination <> '') << 1)
       + ((sar.passenger_type <> '') << 0) as priority
  from seat_allocation_rule sar
       cross join testcase tc
) x order by expected_seat desc, score desc, priority asc;