Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using mySQL, how do I order by date across several different tables?

Tags:

mysql

I'm attempting to generate a dynamic RSS feed for a website, and I'm running into trouble writing a mySQL query for it. I have several seperate tables containing data I need to access, and I want to sort across all of them by date.

As a sample 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'm guessing some kind of full join is going to be needed (and the fact that the times are in different formats is going to make life difficult), but that's about as far as I've gotten.

Any ideas?

Edit: The union idea is good, but I forgot to mention that there is additional information I need to retrieve that can't be made identical between the two -- they require slightly different handling and I need to detect that (easily enough detected by checking for info I know is required in one table, and if the value is null then it's the other type of entry).

Edit edit: Here is my best guess at the appropriate code, but the database is returning an empty table and I don't know why. Left join returns half the info, right join returns the other half, and if I use a full join I should be getting all of it, not none.

select *
from chapter
full join newsFeed
on releaseDate=newsFeed.release
limit 0, 100
like image 939
RonLugge Avatar asked Oct 14 '22 23:10

RonLugge


2 Answers

SELECT ChapterTitle AS Title, release AS `date` 
  FROM Chapters AS sq
UNION ALL
SELECT Title, `DATETIME` 
  FROM NewsFeed
ORDER BY `date`
like image 70
Mchl Avatar answered Oct 18 '22 13:10

Mchl


After a lot of work, I finally found the right query. The problem turns out to be that mySQL doesn't support full outer joins (WTF?) so I had to union them together. in order to perform a sort on that union, I wound up having to wrap that union in it's own SELECT clause, which caused trouble with unnamed derived sub-tables.

The final code comes out to:

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
like image 24
RonLugge Avatar answered Oct 18 '22 15:10

RonLugge