Search code examples
mysqlsqlselecttop-n

Select or create a view of last 3 articles from more than 1 table

I have two tables. One has articles (article), what are static pages on website. Second has blog articles (blog_article), which articles are shown just in the blog page.

Now I want to show latest articles order by date on my homepage. It is easy to show last 3 from article or blog_article, but I have problem to do it from both at once.

Both tables have same structure, except for article, where I want to get rows only with cat_id = 3.

This is article:

|----------------------------------------------------------|
| id | name      | content       | date      | cat_id      |
|----------------------------------------------------------|
| 1  | Test 1    | ...           | 2016-01-24 18:26:00 | 3 |
| 2  | Test 2    | ...           | 2016-01-29 18:26:00 | 3 |
| 3  | Test 3    | ...           | 2016-02-07 18:26:00 | 4 |
| 4  | Test 4    | ...           | 2016-02-18 18:26:00 | 1 |
| 5  | Test 5    | ...           | 2016-03-15 18:26:00 | 3 |
|----------------------------------------------------------|

This is blog_article:

|----------------------------------------------------------|
| id | name      | content       | date                    |
|----------------------------------------------------------|
| 1  | Blog 1    | ...           | 2016-03-01 18:26:00     |
| 2  | Blog 2    | ...           | 2016-03-10 18:26:00     |
| 3  | Blog 3    | ...           | 2016-04-08 18:26:00     |
|----------------------------------------------------------|

What I expect to return:

|----------------------------------------------------------|
| id | name      | content       | date                    |
|----------------------------------------------------------|
| 1  | Blog 3    | ...           | 2016-04-08 18:26:00     |
| 2  | Test 5    | ...           | 2016-03-15 18:26:00     |
| 3  | Blog 2    | ...           | 2016-03-10 18:26:00     |
|----------------------------------------------------------|

I tried this command

SELECT article.name AS name, blog_article.name AS name
FROM article,
     blog_article
WHERE article.cat_id = 3
ORDER BY article.date DESC
LIMIT 3

but no help. I'm not really good in SQL, I use only SELECT, UPDATE, DELETE... basic commands.

Or create a view, but still, no success with command above.

Solution

  • You have the right idea using order by and limit but a wrong one with the (implicit) join. Logically, you'd want to treat articles and blog articles the same, meaning you need to union all between them:

    SELECT   name
FROM     (SELECT name, date FROM article WHERE cat_id = 3
          UNION ALL
          SELECT name, date FROM blog_article) t
ORDER BY date DESC
LIMIT    3