Mysql – Retrieving 10 records from multiple tables

join;MySQL

I have two tables where I store post data. What I want to do is to get 10 records from those two tables. Table 1 – all posts, Table 2 posts that user read.
There are some scenarios;

  • User has never read a post. So all the records will be gotten from table 1.
    This is easy: select * from table1;

  • User has read some posts but we don't know how many. In this case maximum 3 posts will be fetched from table 2, and the rest of the records will come from table 1.

    We can do this by counting how many posts there are in table 2, if there are more than 3 records, get only 3 of them (if lower than 3, then get how many records there are), do the math to learn how many records will be fetched from table 1 and a second query for table 1 to get 7 (or more). But in this case we combine PHP with MySQL and do more work.

How can I get this done in one query?

Best Answer

With a slight waste of fetched rows, you can:

SELECT * FROM (
  SELECT * FROM table2 WHERE <your_own_condition> ORDER BY <your_own_condition> LIMIT 3
  UNION ALL
  SELECT * FROM table1 WHERE <your_own_condition> ORDER BY <your_own_condition> LIMIT 10
) select_both
LIMIT 10
;

At the worst case we will be reading 13 rows, then filtering them down to 10.

Related Question