title: MySQL Nested CASE
body: <p>I am having a bit of an issue with nested CASE in MySQL. </p>
  
  <p>The rest_opening_hours table is as follows:</p>
  
  <pre><code>rest_opening_hours (
    restid int,
    day_of_week int,
    hours_open time,
    hours_close time,
  )
  </code></pre>
  
  <p>I am then trying to perform a query. The reason for the WHEN DAYOFWEEK( NOW()) = 1 bit is that I notice that if you try to take away on a Sunday (day 1) it just returns 1, which causes issues. </p>
  
  <pre><code>SELECT h.hours_close
  FROM restaurants s
  INNER JOIN rest_opening_hours h
  ON s.id = h.restid
  WHERE 
  CASE 
      WHEN h.hours_close &gt; h.hours_open
      THEN h.day_of_week = DAYOFWEEK(NOW()) 
  ELSE 
      CASE 
          WHEN DAYOFWEEK(NOW()) = 1
          THEN h.day_of_week = 7
      ELSE
          h.day_of_week = DAYOFWEEK(NOW() - 1) 
      END 
  END 
  AND s.id = '2'
  LIMIT 0 , 30
  </code></pre>
  
  <p>Here is some data from the rest_opening_hours table:</p>
  
  <pre><code>INSERT INTO `rest_opening_hours` (`restid`, `day_of_week`, `hours_open`, `hours_close`) VALUES
  (2, 1, '17:00:00', '23:00:00'),
  (2, 7, '17:00:00', '06:00:00'),
  (2, 6, '17:00:00', '00:00:00'),
  (2, 5, '17:00:00', '01:00:00'),
  (2, 4, '17:00:00', '02:00:00'),
  (2, 3, '03:00:00', '23:00:00'),
  (2, 2, '17:00:00', '04:00:00');
  </code></pre>
  
  <p>My only problem is, that my query is returning multiple records, and I don't really see why.  The query returns (on a Sunday - day 1) the results for Sunday (day 1) and also for Saturday (day 7) not just day 7 as I had intended in my query!?</p>
  
  <p>I would expect the result "06:00" to be returned, as today it is Sunday (day 1) and following the logic, when DAYOFWEEK(NOW()) = 1 then we select where h.days_of_week = 7 (ie, Yesterday's opening hours) as we previously deduced that h.hours_close &lt; h.hours_open. However, I am getting two results: "06:00" and "23:00" which makes no sense to me, it's almost as if MySQL is ignoring the second case and just returning both!??!?!</p>
  
  <p>Can anyone shed any light on this, as I have spent ages playing around with this and I don't seem to be getting anywhere.</p>
  
  <p>Thanks in advance!</p>
  
  <p>Ryan</p>
  
  <p>Amended Query</p>
  
  <pre><code>SELECT h.hours_close
  FROM restaurants s
  INNER JOIN rest_opening_hours h ON s.id = h.restid
  WHERE 
  CASE 
      WHEN h.hours_close &gt; h.hours_open
      THEN h.day_of_week = DAYOFWEEK( NOW( ) ) 
      ELSE h.day_of_week = DAYOFWEEK( DATE_SUB( NOW( ) , INTERVAL 1 DAY ) ) 
  END 
  AND s.id =  '2'
  </code></pre>
  
  <p>It appears that my issue is that my query is selecting both the day before (7) and the day in question (1) because one is h.hours_close > h.hours_open and the other is h.hours_close &lt; h.hours_open - any ides on how I could isolate this to just one result, perhaps a restructure of sorts so that I am only looking at the one for the day before IF the closing time is less than the opening time (that is, it closes the next day).</p>
  
  <p>In Pseudo-code:</p>
  
  <pre><code>if (closingtime &lt; openingtime) {
  return closing time from yesterday
  } else {
  return closing time from today
  }
  </code></pre>
  
  <p>it is so simple - why is it causing me so many damn problems!?!?!?</p>