title: NOT IN FUNCTION in MySQL
body: <p>I am having a problem with a sql query</p>
  
  <p>Here is my query:</p>
  
  <pre><code>SELECT `rooms`.*  
  FROM `rooms`  
  WHERE (`rooms`.`room_id` NOT IN (
      SELECT * 
      FROM `reservations` `res` 
      WHERE ('2012-05-03' NOT BETWEEN res.expected_checkin_date 
      AND res.expected_checkout_date) 
      AND ('2012-05-08' NOT BETWEEN res.expected_checkin_date 
      AND res.expected_checkout_date)))
  </code></pre>
  
  <p>The problem is that when the second <code>select</code> returns null, it shows the following error:</p>
  
  <p><code>#1241 - Operand should contain 1 column(s)</code></p>
  
  <p>Can anybody help me?</p>
  
  <p><code>EDIT</code></p>
  
  <p>When I run this query it shows all the rows even the one which includes inside of the second <code>select</code>
  I don't know why?</p>
  
  <pre><code>SELECT `rooms`.*, `type`.*, `status`.* FROM `rooms` INNER JOIN `room_types` AS `type` ON `type`.`room_type_id` = `rooms`.`room_type_id` AND `type`.`num_beds` &gt;= 1 AND type.room_type_id=2 INNER JOIN `room_statuses` AS `status` ON `status`.`room_status_id` = `rooms`.`room_status_id` WHERE (`rooms`.`room_id` NOT IN (SELECT `res`.`room_id` FROM `reservations` `res` WHERE (res.expected_checkin_date &gt;= '2012-05-01' AND res.expected_checkin_date &lt; '2012-05-31') AND (res.expected_checkout_date &gt;= '2012-05-31' AND res.expected_checkout_date &lt; '2012-05-31')))
  </code></pre>