Joining 2 select queries on 2 different tables in PostgreSQL

Question

I have two separate queries on 2 different tables which I am trying to join into one query. Both tables are within the same schema.

I am trying to form a query which will return the forumid, threadid and subject of the most recent post in a forum. I can use the two queries I wrote below, but for the sake of efficiency I would prefer to use just one if possible.

The following are my queries:

1>
SELECT forumid,threadid
FROM threadtable
WHERE modifieddate = (select max(modifieddate) from threadtable);

2>
SELECT subject
FROM messsagetable
WHERE modifieddate = (select max(modifieddate) from messsagetable);

I have tried a few solutions but seem to be going round in circles. Any suggestions appreciated. Version is Postgres 8.1.

Answer 1

To combine the results unconditionally use a CROSS JOIN:

SELECT *
FROM  (
    SELECT forumid, threadid
    FROM   threadtable
    ORDER  BY modifieddate DESC
    LIMIT  1
    ) t
CROSS JOIN (
    SELECT subject
    FROM   messsagetable
    ORDER  BY modifieddate DESC
    LIMIT  1
    ) m;

I modified the base queries with the faster and simpler ORDER BY / LIMIT 1. If there are multiple rows in either table sharing the maximum modifieddate, an arbitrary one will be picked.
You could add more items to the ORDER BY clause to pick a certain row in such a case.

Update after comment

however the modifieddate field is in both tables and in the case of the entries I am trying to retrieve it will be the same.

That's a broken design. You need a current version of Postgres and you need to reconsider your database layout.
As for now, the above query still does the job as requested - once you got your table names straight.