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
>
