join w/ subquery

[snowboardr]

What i am going to do here is Select all replys by a user, but don't display posts they "started", the first SQL was for ones they started.

Main post is signified by f.ptype=0
A reply is f.ptype=1 a reply will also have the main post's id in the column f.preplyid

Its erroring on the subquery
-----------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT fp.pid, fp.puid, fp.ptype FROM forumpost as fp WHERE fp.
--------------------------

SELECT 
f.pid, 
f.ptid, 
f.ptid2, 
f.puid, 
f.psubject, 
f.ptype, 
f.preplyid, 
f.ptime, 
t1.tid, 
t1.tname 
tname1, 
t2.tid, 
t2.tname 
tname2 
FROM 
forumpost as f, 
thread as t1, 
thread as t2 
WHERE t1.tid = f.ptid AND t2.tid = f.ptid2 
AND 
f.puid =2 
AND 
f.ptype=1 
AND f.puid NOT IN 
(SELECT fp.pid, fp.puid, fp.ptype FROM forumpost as fp WHERE fp.ptype=0 AND fp.puid=f.preplyid) 

ORDER by f.ptime desc LIMIT 7

 

[TonyGroves]

What MySQL version are you using? Subqueries like that are only available in v. 4.1 or later.

 

[snowboardr]

Oh no! that would make since... just my luck i have 4.0.24 hmm what other method do you suggest?

 

[snowboardr]

Ok i installed 4.1.21 and its working, however its still selecting posts replies that were started by the same person

SELECT f.pid, f.ptid, f.ptid2, f.puid, f.psubject, f.ptype, f.preplyid, f.ptime, t1.tid, t1.tname tname1, t2.tid, t2.tname tname2 FROM forumpost as f, thread as t1, thread as t2 WHERE t1.tid = f.ptid AND t2.tid = f.ptid2 AND f.puid =2 AND f.ptype=1 AND f.puid NOT IN (SELECT fp.puid FROM forumpost as fp WHERE fp.ptype=0 AND fp.puid=f.preplyid) ORDER by f.ptime desc LIMIT 7

Do you got a million? www.gotamillion.com/?r=T

 

[snowboardr]

i got it working