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?