From: Peter Brawley
Date: June 4 2007 6:59pm
Subject: Re: Issue with SubSelect Quey
List-Archive: http://lists.mysql.com/mysql/207219
Message-Id: <46646112.8070605@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------070003010709090806040507"

--------------070003010709090806040507
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>   

--------------070003010709090806040507--