[SQLITE2] Complicated SELECT with 2 sub-queries

RobertK · 09-18-2008, 05:26 PM

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).

RobertK · 09-19-2008, 09:29 PM

Okay, I understand someone with inadequate knowledge of sub-selects leaving this topic untouched, but what about everyone else? SQLite supports 98% of MySQL's syntax.

I did manage to query within a forum for threads and their most recent poster. However, I'm still uncertain how to do this from the index without a per-forum query.

Here's the forum-specific query. However, I suspect that it'll return a result for each post in the thread.

sql Code:

SELECT DISTINCT(posts.tid) AS 'id', 
threads.threadname AS 'name', 
posts.posted AS 'posted', 
posts.uid AS 'uid', 
threads.posts AS 'posts', 
(SELECT username FROM users WHERE users.uid = uid) AS 'who', 
threads.flags AS 'flags' FROM posts 
JOIN threads ON posts.tid = threads.tid 
WHERE threads.fid = {$fid} 
ORDER BY posts.posted ASC

sketchMedia · 09-24-2008, 04:24 PM

EDIT:::: nvm i misread your second post. ehhhh long day.

RobertK · 09-24-2008, 05:42 PM

It happens.

Have any ideas about the first? I've got the second query working great now, but I haven't actually made progress on the second one.