16

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.

Improve this question
8
  • 3
    What exactly you want to do? Does these tables have any field that tie together each other? Commented May 20, 2013 at 16:23
  • 1
    If there is to be a join, we need to know what fields the tables have in common. Look for a foreign key or any field names that are the same. Is threadid in the messagetable? Commented May 20, 2013 at 16:42
  • 2
    Version 8.1 is long dead and forgotten. You should update to a current version. Also: can there be multiple rows per query or is a single row guaranteed? Commented May 20, 2013 at 16:42
  • 1
    concerning your comment in Erwins answer: is there a relation between messagetable and threadtable? e.G. does messagetable have a foreign key that connects it to an entry in threadtable? Commented May 20, 2013 at 18:58
  • 2
    @James Then how do you know what message belongs to what thread? How do you display the contents (all messages in a thread)? Commented May 20, 2013 at 19:11

2 Answers 2

Reset to default
27 
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks Angelo, they don't work however. The first one gives me the following: "SQL error: ERROR: subquery in FROM must have an alias HINT: For example, FROM (SELECT ...) [AS] foo." ....and the second gives the following: "SQL error: ERROR: relation "messsagetable" does not exist"
Thanks for the fast response, still not working though: "SQL error: ERROR: syntax error at or near "b" LINE 7: ...odifieddate = (SELECT MAX(modifieddate) FROM messsagetable) b" - I wonder if it's a postgres version issue. I can't upgrade however, my project is on a shared server so it's up to the hosting co. to upgrade.
Sorry, but it's now returning the same error as the second SQL code: "SQL error: ERROR: relation "messsagetable" does not exist"
@James Your original query stated that table, test your original query and correct it. If it works, than this one will work as well.
@James: You should urgently quit a hosting company that's still running 8.1. That version has been out of service for a couple of years now and is a severe security hazard - besides being a waste of time. Current versions of Postgres have gotten so much better.
|
12

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.

Improve this answer

6 Comments

This SQL is cool, but we don't really know what the OP is asking for. Joining on a date field seems like a bad idea in the context of a forum/messaging system. (Or no real join at all)
Thanks Erwin, however when I try the above I am getting the following: "ERROR: relation "messsagetable" does not exist." What I am trying to do is 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 above, but for the sake of efficiency I would prefer to use just one if possible.
@James You should write that last comment into your question, its very relevant.
@James: As for the error message: Any chance your table's name is actually messagetable?
Im getting subquery in FROM must have an alias on postres 10.5
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.