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?

  • Do you really need to duplicate the questions on two tables? You should consider having foreign keys in the second table pointing to the questions marked as read. Duplicated information does not looks like being a good idea.
    – jap1968
    Jun 1, 2013 at 16:08
  • I don't need the duplicate content. I want to avoid it. I did what I wanted with combining PHP and two different MySQL queries. But I want to do that with one SQL query.
    – kent ilyuk
    Jun 1, 2013 at 16:18

1 Answer 1


With a slight waste of fetched rows, you can:

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

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

  • This query can return duplicate rows. How can we avoid it?
    – kent ilyuk
    Jun 1, 2013 at 13:34
  • And also, the tables have different columns. That's why it doesn't work.
    – kent ilyuk
    Jun 1, 2013 at 13:43
  • If the tables have different rows, that information is relevant and it should have appeared on your question.
    – jap1968
    Jun 1, 2013 at 16:06
  • 3
    Avoid duplicate rows by using UNION instead of UNION ALL (this eliminates rows based on ALL returned columns). Fix the * problem by listing the specific columns. You gave no description for your table structure -- I provide with a general solution. Jun 1, 2013 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.