TalkPHP - View Single Post - [SQLITE2] Complicated SELECT with 2 sub-queries

RobertK · 09-19-2008, 08: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

09-19-2008, 08:29 PM	#2 (permalink)
RobertK The Addict Join Date: Jan 2008 Location: USA Posts: 217 Thanks: 16	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 __________________ 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