Geek Answers Handbook

Using mySQL, how do I sort by date in several different tables?

I am trying to create a dynamic RSS feed for a website and I am faced with the problem of writing a mySQL query for it. I have several separate tables containing the data that I need to access, and I want to sort them by date.

As an example of a table:

Chapters
release ChapterTitle ChapterContent  ExtraInfo
10/12 'Chapter 1'  'Bla bla bla'  StoryTitle
10/14 'Chapter 2'  'Bla bla bla'  StoryTitle
10/16 'Chapter 3'  'Bla bla bla'  StoryTitle
10/18 'Chapter 4'  'Bla bla bla'  StoryTitle

NewsFeed
DATETIME Title Article
10/11 'Website Online!'  'I now have my website!'
10/15 'Everything Working!'  'Everything is going well!'

I want the sort to return things in the following order:

Website Online  Text
Chapter 1 StoryTitle  Text
Chapter 2 Story Title  Text
Everything Working  Text
Chapter 3 Story Title  Text
Chapter 4 Story Title  Text

I suppose that some kind of complete connection will be necessary (and the fact that time in different formats makes life difficult), but this is about the way I got it.

Any ideas?

Edit: , , , , - , ( , , , null, ).

: , , . Left join , , , , none.

select *
from chapter
full join newsFeed
on releaseDate=newsFeed.release
limit 0, 100
+5
2

. , mySQL (WTF?), . , SELECT, .

:

select *
from
(
    select * from
    (
        select chapterTitle, storyID, chapterContent, newsFeed.article, newsFeed.Title, newsFeed.release
        from chapter
        right join newsFeed
        on releaseDate=newsFeed.release
    ) AS derivedTable1
    union
    select * from 
    (
        select chapterTitle, storyID, chapterContent, newsFeed.article, newsFeed.title, releaseDate
        from chapter
        left join newsFeed
        on releaseDate=newsFeed.release
    ) AS derivedTable2
) as MainTable
order By MainTable.release desc
+1
SELECT ChapterTitle AS Title, release AS `date` 
  FROM Chapters AS sq
UNION ALL
SELECT Title, `DATETIME` 
  FROM NewsFeed
ORDER BY `date`
+3

More articles:


All Articles