Hi everyone, it's been a long time but I'm getting back into the swing of things. Started my own company, so it has been hectic of late.
I'm struggling with a complicated sub-query that traces through 3 tables to get the last poster in a forum. Everything else works absolutely great. Please be patient with me, since my brain is rather scrambled with this cryptic logic.
CREATETABLE posts ( pid INTEGER PRIMARYKEY, tid INTEGER, uid INTEGER, posted INTEGER, ip VARCHAR(30), content TEXT )
Okay, got that? Here's the query. I'll even mark the second subquery, because I've no clue how to write it now.
SELECT fid, title, description, ( SELECT COUNT(*)FROM threads WHERE threads.fid = fid )AS"count", (-- The trouble is right here... SELECT users.username AS"name", users.uid AS"uid" FROM threads JOIN users ON uid WHERE-- how do I limit this by the -- last post within the thread? ) AS"lastposter"FROM forums ON fid
What I'm TRYING to do is this: get the ID/NAME of the last poster from the database. Currently, the software is only looking at the "forum list" view, so I don't want to run 18 queries on this. So the query gets all the forums, and then subqueries for the thread count, and then the last poster. Again, the last is my trouble.
So I'm kind of stuck. How should I rewrite this query? I organized my DB this way for simplicity, and because I'm still only novice/intermediate with MySQL/SQLite.
And just for the record, I know that just about the last thing the world needs is yet another forum package, but I'm writing this to learn a few key concepts (that aren't SQL).
__________________ Programmers are in a race with the Universe to create bigger and better idiot-proof programs, while the Universe is trying to create bigger and better idiots. So far the Universe is winning. - Rich Cook