SELECT -ing from 2 tables

[designaweb]

Hi,

I am building a website that has a software download category, and has news on the main page. Now I have two tables, named "news" and "software". On the main page I want to select the latest 40 updated items, so whether it is a newspost from table "news" or an extra entry in table "software", or an updated version of an entry in "software". To keep track of the dates I use a field "postdate" in table "news", and in the "software" table I have one field called "postdate", and one field called "updateddate". They are all INT types holding a time() string. Now what I want is a mysql SELECT query that selects the 40 latest items...

I am struggling... Is this even possible?

[Kalashnikov]

if it is even possible, but for what?
why not to select three times from those two tables into (temporary) third one and then select from it ordering by cdate?

and btw why not to use TIMESTAMP type instead if INT?
values of columns of this type are inserted/updated automatically.

[peterdaly]

First off, I'm not really familiar with what aspects of SQL MySQL supports, I generally use Postgres, DB2, or Informix.

The sql "UNION" clause should be able to do what you are looking for, although you may have to get a little creative when it comes to the details. I am not sure how to specify limits in MySQL. For example:

SELECT subject as title, postdate as postdate from news
UNION
SELECT softwaretitle as title, postdate as postdate from software
ORDER BY postdate desc;

What's important is that all of the column names, types, and order match up on each union statement.

Hope this gives you a start.

[designaweb]

Thanks to the both of you!

Here's the query I use now, doing a UNION statement wasn't an option since my host is still on mySQL 3 :(

---------------

mysql_query("CREATE TEMPORARY TABLE temp_union TYPE=HEAP SELECT postdate AS postdate, seo_title as seo_title, title as title FROM news ORDER BY postdate DESC LIMIT 50");
mysql_query("INSERT INTO temp_union SELECT postdate AS postdate, seo_title as seo_title, title as title FROM software ORDER BY postdate DESC LIMIT 50");
mysql_query("INSERT INTO temp_union SELECT dateupdated AS postdate, seo_title as seo_title, title as title FROM software ORDER BY postdate DESC LIMIT 50");
$qry = "SELECT * FROM temp_union ORDER BY postdate DESC LIMIT 50";
$res = mysql_query($qry) or die (mysql_error());
mysql_query("DROP TABLE temp_union");

------------