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
SELECT ChapterTitle AS Title, release AS `date`
FROM Chapters AS sq
UNION ALL
SELECT Title, `DATETIME`
FROM NewsFeed
ORDER BY `date`
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With