title: MySQL Multiple Case When Exists Statement
body: <p>I have two tables. Let's call it: SEATS and SEAT_ALLOCATION_RULE table.</p>
  
  <p>Below are the table schema:</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>SEAT_ALLOCATION_RULE table determines which seat should a passenger be assigned to based on the following order in priority:     </p>
  
  <pre><code>1. Origin, destination, and  passenger_type match
  2. Origin and destination match
  3. Origin match
  </code></pre>
  
  <p>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:</p>
  
  <ul>
  <li>If the Origin is Malaysia, Destination is Singapore, and Passenger_Type is Senior_Citizen, it should return seatID 3</li>
  <li>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)</li>
  <li>If the Origin is Malaysia, Destination is US, and Passenger_Type is Student, it should return seatID 1 (since it only match Origin).</li>
  </ul>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>