title: mysql union limit problem
body: <p>I want a paging script working properly basically but the situation is a bit complex.   I need to pick data from union of two sql queries.   See the query below.  I have a table book and a table bookvisit.  What I want is here to show all books for a particular category in their popularity order.   I am getting data for all books with atleast one visit by joining table book and bookvisit.  and then union it with all books with no visit.  Everything works fine but when I try to do paging, I need to limit it like (0,10) (10,10) (20,10) (30,10), correct?   If I have 9 books in bookvisit for that category and 3761 books without any visit for that category (total of 3770 books), it should list 377 pages , 10 books on each page.   but it does not show any data for some pages because it tries to show books with limit 3760,10 and hence no records for second query in union.   May be I am unable to clear the situation here but if you think a bit about the situation, you will get my point.</p>
  
  <pre><code>SELECT * FROM ( 
  SELECT * FROM (
   SELECT viewcount, b.isbn, booktitle, stock_status, price, description FROM book AS b 
   INNER JOIN bookvisit AS bv ON b.isbn = bv.isbn WHERE b.price &lt;&gt; 0 AND hcategoryid = '25' 
   ORDER BY viewcount DESC 
   LIMIT 10, 10
   ) AS t1 
   UNION 
   SELECT * FROM 
   ( 
   SELECT   viewcount, b.isbn, booktitle, stock_status, price, description FROM book AS b 
   LEFT JOIN bookvisit AS bv ON b.isbn = bv.isbn WHERE b.price &lt;&gt; 0 AND hcategoryid = '25' 
   AND viewcount IS NULL 
   ORDER BY viewcount DESC 
   LIMIT 10, 10
    ) AS t2 
    ) 
    AS qry
     ORDER BY viewcount DESC 
  LIMIT 10
  </code></pre>