Re: BUG using left join.
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 '' group by name Instead of and t_boxdaily.boxdate '' 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
BUG using left join.
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: namemax(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: namemax(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. - 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
RE: BUG using left join.
Is the date read the same with or without the dashes? -Original Message- From: Steve Briant [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 9:17 AM To: [EMAIL PROTECTED] Subject: BUG using left join. 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: namemax(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: namemax(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. - 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 - 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
Re: BUG using left join.
You are passing a string to compare to a date. If you do this, pass it in the string format. Other wise and t_boxdaily.boxdate 19990101 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. - 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 - 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
