Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 '13 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 '13 at 16:18
add comment

1 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.

share|improve this answer
    
This query can return duplicate rows. How can we avoid it? –  kent ilyuk Jun 1 '13 at 13:34
    
And also, the tables have different columns. That's why it doesn't work. –  kent ilyuk Jun 1 '13 at 13:43
    
If the tables have different rows, that information is relevant and it should have appeared on your question. –  jap1968 Jun 1 '13 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. –  Shlomi Noach Jun 1 '13 at 16:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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