title: MySQL - Combining two select statements into one result with LIMIT efficiently
body: <p>For a dating application, I have a few tables that I need to query for a single output with a LIMIT 10 of both queries combined. It seems difficult to do at the moment, even though it's not an issue to query them separately, but the LIMIT 10 won't work as the numbers are not exact (ex. not LIMIT 5 and LIMIT 5, one query may return 0 rows, while the other 10, depending on the scenario).</p>
  
  <pre><code>members table
  member_id | member_name
  ------------------------
       1         Herb
       2         Karen
       3         Megan
  
  dating_requests
  request_id | member1 | member2 | request_time
  ----------------------------------------------------
       1          1         2      2012-12-21 12:51:45
  
  dating_alerts
  alert_id | alerter_id | alertee_id | type | alert_time
  -------------------------------------------------------
      5           3            2     platonic  2012-12-21 10:25:32
  
  dating_alerts_status
  status_id | alert_id | alertee_id | viewed | viewed_time
  -----------------------------------------------------------
       4          5           2          0      0000-00-00 00:00:00 
  </code></pre>
  
  <p>Imagine you are Karen and just logged in, you should see these 2 items:</p>
  
  <pre><code>1. Herb requested a date with you.
  2. Megan wants a platonic relationship with you.
  </code></pre>
  
  <p>In one query with a LIMIT of 10. Instead here are two queries that need to be combined:</p>
  
  <pre><code>1. Herb requested a date with you.
     -&gt; query = "SELECT dr.request_id, dr.member1, dr.member2, m.member_name 
                 FROM dating_requests dr 
                 JOIN members m ON dr.member1=m.member_id 
                 WHERE dr.member2=:loggedin_id 
                 ORDER BY dr.request_time LIMIT 5";
  2. Megan wants a platonic relationship with you.
     -&gt; query = "SELECT da.alert_id, da.alerter_id, da.alertee_id, da.type,
                        da.alert_time, m.member_name
                 FROM dating_alerts da
                 JOIN dating_alerts_status das ON da.alert_id=das.alert_id
                      AND da.alertee_id=das.alertee_id
                 JOIN members m ON da.alerter_id=m.member_id
                 WHERE da.alertee_id=:loggedin_id AND da.type='platonic'
                       AND das.viewed='0' AND das.viewed_time&lt;da.alert_time 
                 ORDER BY da.alert_time LIMIT 5";
  </code></pre>
  
  <p>Again, sometimes both tables may be empty, or 1 table may be empty, or both full (where LIMIT 10 kicks in) and ordered by time. Any ideas on how to get a query to perform this task efficiently? Thoughts, advice, chimes, optimizations are welcome.</p>