debute debute - 7 months ago 4
SQL Question

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.

Answer

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
Comments