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.
Here's the table structure:
sql Code:
CREATE TABLE users (
uid INTEGER PRIMARY KEY,
username VARCHAR(255),
password VARCHAR(40),
email VARCHAR(255),
signature TEXT,
timeoffset VARCHAR,
dst BOOLEAN,
datefmt VARCHAR(128),
role INTEGER,
spam INTEGER,
since INTEGER,
diskspace INTEGER
)
CREATE TABLE threads (
tid INTEGER PRIMARY KEY,
uid INTEGER,
fid INTEGER,
threadname VARCHAR(255),
posts INTEGER,
flags INTEGER
)
CREATE TABLE posts (
pid INTEGER PRIMARY KEY,
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.
sql Code:
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).