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.
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 results from the first with all results from 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
would combine all results from the first with all results from the second that have the same modifieddate.
As both queries return only one resultrow, you most likely want the first suggestion.
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.
