Hi, Note that if you are using a condition on a t_boxdaily field in the where clause, i think that your "left join" will not take the desired effect. If there is a record in table t_fund that it's not in t_boxdaily then the t_boxdaily fields would appear in the result with NULL values (because of the left join). But if you use a condition on table t_boxdaily in the where clause, those rows will not be in the result set.
Try this using Q1: select name, sum(repurchasedunits) from t_fund left join t_boxdaily on t_fund.fundid = t_boxdaily.fundid where t_fund.fundid = 'BB01' group by name and Q2: select name, sum(repurchasedunits) from t_fund left join t_boxdaily on t_fund.fundid = t_boxdaily.fundid where t_fund.fundid = 'BB01' and t_boxdaily.boxdate > '00000000' group by name Instead of "and t_boxdaily.boxdate > '00000000'" choose a condition that is always true. The goal is that you see different results in Q1 and Q2... Steve Briant wrote: > > >Hi, > > > >I think I may have spotted a bug in connection with the LEFT JOIN statement. > > > >The following SQL > > > >select name, sum(repurchasedunits) > >from t_fund left join t_boxdaily on t_fund.fundid = t_boxdaily.fundid > >where t_fund.fundid = 'BB01' > >group by name > > > >produces the output as below: > > > >name max(boxdate) sum(repurchasedunits) > >---- ------------ --------------------- > >Marleborough Fund Managers (BB01) 2002-04-12 0 > > > >(1 row(s) affected) > > > > > >However, a slight change to the join as below: > > > >select name, sum(repurchasedunits) > >from t_fund left join t_boxdaily on t_fund.fundid = t_boxdaily.fundid > >where t_fund.fundid = 'BB01' and t_boxdaily.boxdate < '19990101' > >group by name > > > >produces this output: > > > >name max(boxdate) sum(repurchasedunits) > >---- ------------ --------------------- > > > >(0 row(s) affected) > > > > > > > >As detailed in the release notes, this join should still give a match as per > >the first example shouldn't it ? What is causing it not to is that I have no > >records in t_boxdaily that have a boxdate previous to 1st January 1999. > > > >I am running MySQL version 3.23.37 under Windows 2000. > > > >Regards > > > >Steve Briant. -- dsoares (sql) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
