List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:June 4 2007 6:59pm
Subject:Re: Issue with SubSelect Quey
View as plain text  
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
>   

Thread
Issue with SubSelect Queyleeladharan acharya4 Jun
  • Re: Issue with SubSelect QueyPeter Brawley4 Jun