Combining two queries on two different tables in PostgreSQL

Question

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

I am trying to form a request that will return forumid, threadid and the topic of the last forum post. I can use the two queries that I wrote below, but for the sake of efficiency, I would prefer to use only one, if possible.

Following are my inquiries:

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 tried several solutions, but it seems to be all around. Any suggestions appreciated. Postgres 8.1 version.

Answer 1

To accurately combine results, use 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 changed basic queries faster and easier ORDER BY / LIMIT 1 . If in one table there are several rows sharing the maximum modifieddate , an arbitrary one will be selected.
You can add multiple items to the ORDER BY to select a specific row in this case.

Update after comment

however, the modifieddate field is in both tables, and in the case of the records that I am trying to recover, they will be the same.

This is a broken design. You need the current version of Postgres, and you need to rethink the database structure.
At the moment, the above query still performs the task in accordance with the query - as soon as you get your table names.

Answer 2

 SELECT * FROM (SELECT forumid,threadid FROM threadtable WHERE modifieddate = (SELECT MAX(modifieddate) FROM threadtable)) a, (SELECT subject FROM messsagetable WHERE modifieddate = (SELECT MAX(modifieddate) FROM messsagetable)) b 

would combine all the results from the first with all the results of the second

 SELECT * FROM (SELECT forumid,threadid, modifieddate FROM threadtable WHERE modifieddate = (SELECT MAX(modifieddate) FROM threadtable)) a INNER JOIN (SELECT subject, modifieddate FROM messsagetable WHERE modifieddate = (SELECT MAX(modifieddate) FROM messsagetable)) b ON a.modifieddate = b.modifieddate 

I would combine all the results from the first, with all the results of the second, which have the same changes.

Since both queries return only one result, you most likely want the first sentence.