title: SQL Query - Compare all rows from temp table
body: <p>I have a table <code>Rooms</code> and a temporary table <code>Room_Nums</code></p>
  
  <p>Columns for <code>Rooms</code> table:</p>
  
  <pre><code>Rooms(
   ID,
   room_name,
   max_Adults,
   max_Children
  );
  
  -- Populate
  INSERT INTO Rooms VALUES (1,"Room A",2,1),(2,"Room B",2,5),(3,"Room C",3,1);
  </code></pre>
  
  <p>Temporary table:</p>
  
  <pre><code>DROP TEMPORARY TABLE IF EXISTS Room_Nums;
  CREATE TEMPORARY TABLE Room_Nums(
   ID int(1),
   Adults int(2),
   Children int(2)
  );
  
  -- Populate
  INSERT INTO Room_Nums VALUES (1,2,0),(2,2,5);
  </code></pre>
  
  <hr>
  
  <p>I want to select <strong>only</strong> the <code>Rooms</code> that have <code>max_adults</code> + <code>max_children</code> smaller or equal (<code>&lt;=</code>) from all the rows in <code>Room_Nums</code> along with the ID from <code>Room_Nums</code></p>
  
  <p><strong>EDIT</strong><br>
  It's MySQL.</p>
  
  <p>If <code>Room_Nums</code> has 2 rows it means I'm asking for 2 rooms that match <code>Adults</code> + <code>Children</code> ex.</p>
  
  <pre><code>SELECT r.room_name, rn.Adults, rn.Children, rn.ID
  FROM Rooms r
       OUTER JOIN Room_Nums rn 
           ON (rn.Adults &lt;= r.max_Adults AND rn.Children &lt;= r.max_Children )
  </code></pre>
  
  <p><strong>EDIT 2</strong><br>
  Asking for:    </p>
  
  <ul>
  <li>1 Room: 2 Adults &amp; 0 Children</li>
  <li>1 Room: 2 Adults &amp; 5 Children</li>
  </ul>
  
  <p>The result should be:   </p>
  
  <pre><code>-----------------------------------------------
  | Room Name | Adults | Children | Room_Nums.ID |
  -----------------------------------------------
  | Room A    |   2    |    0     |    1         |
  -----------------------------------------------
  | Room B    |   2    |    5     |    1         |
  -----------------------------------------------
  | Room B    |   2    |    5     |    2         |
  -----------------------------------------------
  </code></pre>
  
  <p>Show empty result if it can't find a row from <code>Room_Nums</code>.</p>