PDA

View Full Version : Simple SELECT from two similar tables

industriality
04-27-2006, 03:35 AM
I have two mySQL tables, "news" and "journal". Both tables have these columns: id, date, headline, body. Journal also contains an author column.

How would I construct a simple mySQL SELECT query to pull from both tables at the same time, and order the entire list by date? I think a join may be in order, but I'm confused by the documentation.

The date column in both tables is formatted YYYY-MM-DD.

The result should be able to be formatted like this (simple xml)
<entries>
<entry>
<type>news</type>
<id>53</id>
<name>This is a news entry headline</name>
<body>This is the body text.</body>
<author>This is the author</author>
</entry>
<entry>
...
</entry>
</entries>

This is for a band website. The goal here is to be able to view news and journal entries in one combined listing, and also generate an RSS feed from that combined list.
guelphdad
04-27-2006, 04:37 AM
You would need a union. One thing to note in a union is that you must have the same number of columns. So what you do in your news query is fake the author. then when you loop over your results you would just skip printing out the author.


(select
id, date, headline, body, author
from journal)
union all
(select
id, date, headline, body, 'None' as author
from news)
order by date


one more thing I realized when looking at your specifications, you of course want to know which table they come from:



(select
id, date, headline, body, author, 'journal' as storytype
from journal)
union all
(select
id, date, headline, body, 'None' as author, 'news' as storytype
from news)
order by date
industriality
04-28-2006, 11:28 PM
That looks great, but it's not working for me. All the mySQL documentation says that should work, but it's not for me. Maybe it's a mySQL version thing? I have version 3.23.56-nt running.

I've even simplified the query and I still get the same error. The simplified query is:
(select date, name from news) union all (select date, name from journal)

The error I get is:
#1064 - You have an error in your SQL syntax near '(select date, name from news) union all (select date, name from journal) LIMIT 0' at line 1
industriality
04-28-2006, 11:30 PM
By the way, I ended up doing it differently for the production site but I would still love to know how to do this with mySQL. What I did was I used PHP to output two separate SELECT queries into one associative array, then sorted that array by date. It would be really great to be able to do it all through the one mySQL query though.
guelphdad
04-29-2006, 02:46 AM
your version is completely out of date. it doesn't support unions for one thing, nor sub queries for another. upgrade to mysql 4.1 at a minimum.