Leelu,
>The above Query's subselect query doesn't work properly, it lists
>even the posts whose forum id is in subselect query.
Your `post LEFT JOIN thread ON post.threadid=thread.threadid` asks for
post rows whether they have matching thread rows or not. Are you sure
you want that? It sounds backwards. Isn't your model like this...
a forum may have threads,
a thread may have posts
?
Then wouldn't your query be...
SELECT
post.userid , post.pagetext , post.dateline , post.threadid,
thread.title , thread.forumid
FROM forum
LEFT JOIN thread ON thread.forumid = forum.childlist
LEFT JOIN post ON thread.threadid=post.threadid
WHERE forum.userid <> xyz
AND post.visible=1
AND thread.visible = 1
ORDER BY post.dateline DESC LIMIT 5;
Or do I misunderstand your query?
PB
leeladharan acharya wrote:
Hi,
In MySQL, The following Query
SELECT `post`.`userid` , `post`.`pagetext` , `post`.`dateline`
,`post`.`threadid` , `thread`.`title` , `thread`.`forumid` FROM
`post`LEFT
JOIN `thread` ON `post`.`threadid`=`thread`.`threadid`
WHERE`userid`=`xyz`
AND `thread`.forumid not in(SELECT `childlist` from`forum` where
forumid=`xyz`) AND `post`.`visible`=1 AND`thread`.visible = 1 ORDER BY
`post`.`dateline` DESC LIMIT 5;
The above Query's subselect query doesn't work properly, it lists even
the
posts whose forum id is in subselect query.
If the same Query with inner Query replaced by direct values gives the
expected result set...
Is this problem with My Query or Something else.. ?
Regards
Leelu
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.472 / Virus Database: 269.8.7/830 - Release Date: 6/3/2007 12:47 PM
