MySQL Multiple Table Query and Results

Question

I am trying to query multiple tables and trying to display the results as follows...

Tables...

news
id    title      news_datestamp
1      news 1       01/01/2001
2      news 2       01/05/2001

articles    title          articles_datstamp
1            article 1         01/04/2001
2            article 2         01/06/2001

I'm trying to get a single script to display the results as...

News 1 01/01/2001
Article 1 01/04/2001
News 2 01/05/2001
Article 2 01/06/2001

Any ideas?

It looks like one of two things could be going on here. Do you want all of the items sorted ascending by datestamp, or do you want them sorted primarily by id ascending and then alternating between News and Article? — theftprevention
– theftprevention, Commented Sep 20, 2013 at 19:12
How do these two tables of data relate to each other? Do you need to add a column (i.e., foreign key) to one or both of these tables to account for the relationship between news items and articles? That would make what you appear to be trying to do easier. — James Adam
– James Adam, Commented Sep 20, 2013 at 19:12
Using UNION will show you all the news and right after all the articles, not in between as you show in your example, however if you order that by date then you may have articles and news mixed. If articles belongs to news then you must edit your schema to have a relational one (as James says) using foreign keys. — porfiriopartida
– porfiriopartida, Commented Sep 20, 2013 at 19:45

dognose · Accepted Answer · 2013-09-20 19:31:08Z

1

You could simple use a Union to merge results in a subselect and sort the final table:

SELECT * FROM (
  SELECT id, title, news_datestamp AS datestamp, "news" AS type FROM news
  UNION ALL
  SELECT articles AS id, title, article_datestamp AS datestamp, "article" AS type FROM articles
) AS temptable
ORDER BY datestamp

The type column will help you to identify id references later.

answered Sep 20, 2013 at 19:31

dognose

21k9 gold badges67 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

O. Jones · Accepted Answer · 2013-09-20 19:30:37Z

1

It looks to me like you want a union of the news and article tables.

SELECT * FROM
  ( SELECT title,
           news_datestamp AS datestamp
      FROM news
     UNION ALL
    SELECT title,
           article_datestamp AS datestamp
     FROM articles
    ) AS everything
   ORDER BY datestamp, title

answered Sep 20, 2013 at 19:30

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Collectives™ on Stack Overflow

MySQL Multiple Table Query and Results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related