tags:

views:

76

answers:

2

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
+1  A: 
SELECT ChapterTitle AS Title, release AS `date` 
  FROM Chapters AS sq
UNION ALL
SELECT Title, `DATETIME` 
  FROM NewsFeed
ORDER BY `date`
Mchl
You might want `UNION ALL` rather, though
nos
Use UNION if you need to remove duplicates, and there's no need for brackets on each statement being UNION'd
OMG Ponies
I put the brackets there just to be safe - it shouldn't have any impact on performace. Thanks for editing. I don't think UNION vs UNION ALL would make any difference here, seeing as both tables contain rather distinct data.
Mchl
Thanks! The 'as' keyword was the solution I was looking for. (And union Vs. union all doesn't make a difference; agreed!)
RonLugge
Ooops; spoke too soon. I left out some information in the original question, and union doesn't work here. That said, I suspect the 'As' keyword is still the solution I need.
RonLugge
Well... if you can provide that extra information we could still help ;)
Mchl
I edited in some extra info. At this point, I've gotten it down to 'working' by taking a left join, a right join, and using the union command between them. (Which should be teh same thing as a full join, right?) Unfortunately, I can't convince releaseDate and release fields to be considered identical information for some reason, so I can't sort it.
RonLugge
D'oh, my problem is that 'Full Join' isn't supported by mySQL; now I just have to figure out how to properly append 'ORDER BY' so that it applies to the union construct rather than one of the joins.
RonLugge
+1  A: 

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
RonLugge