Re: REPOST: SELECT syntax
"Thalis A. Kalfigopoulos" wrote: Check you don't have empty strings as empty remarks, but that you have NULLs. Even with empty strings as remarks though you can bypass them with an extra WHERE condition :-) That was it. I was storing empty strings, instead of NULLs. Thanks! AMK4 -- H | Hi, I'm currently out of my mind. Please leave a message. BP! | ~ Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 Director of Internet Operations / SysAdmin. 800.441.3873 x130 Photo Craft Laboratories, Inc. .eFax 248.671.0909 http://www.pcraft.com .3550 Arapahoe Ave, #6 .. . . . .Boulder, CO 80303, U.S.A.
Re: REPOST: SELECT syntax
On Wed, 4 Apr 2001, Ashley M. Kirchner wrote: Db - table1, table2 and table3 SELECT table1.task, table2.comment, table2.remarks, table3.history FROM table1 LEFT JOIN table2 ON table1.id=table2.todoid LEFT JOIN table3 ON table1.id=table3.todoid WHERE table1.id=10; Results (roughly) in: +--++--+-+ | task | comment |remarks | history | +--++--+-+ | task_1 | cmt_1| rmk_1| | | task_1 | cmt_2| | | | task_1 | cmt_3| |hst_1| | task_1 | cmt_4| rmk_2| | | task_1 | cmt_5| |hst_2| | task_1 | cmt_6| | | +--++--+-+ However, what I'd really like to have is the following: +--++--+-+ | task | comment |remarks | history | +--++--+-+ | task_1 | 6 | 2 | 2 | +--++--+-+ How do I change the select clause to have COUNT() in it instead? AMK4 You get your COUNT() by adding the corresponding GROUP BY clause. I assume something like the following should work: SELECT table1.task,COUNT(table2.comment),COUNT(table2.remarks),COUNT(table3.history) FROM table1 LEFT JOIN table2 ON table1.id=table2.todoid LEFT JOIN table3 ON table1.id=table3.todoid WHERE table1.id=10 GROUP BY table1.task; hope it works, thalis - 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: REPOST: SELECT syntax
"Thalis A. Kalfigopoulos" wrote: +--++--+-+ | task | comment |remarks | history | +--++--+-+ | task_1 | cmt_1| rmk_1| | | task_1 | cmt_2| | | | task_1 | cmt_3| |hst_1| | task_1 | cmt_4| rmk_2| | | task_1 | cmt_5| |hst_2| | task_1 | cmt_6| | | +--++--+-+ SELECT table1.task,COUNT(table2.comment),COUNT(table2.remarks),COUNT(table3.history) FROM table1 LEFT JOIN table2 ON table1.id=table2.todoid LEFT JOIN table3 ON table1.id=table3.todoid WHERE table1.id=10 GROUP BY table1.task; Based on the above data, and the above select, I get the following: +--+-+-+---+ | task | comment | remarks | history | +--+-+-+---+ | task_1 | 6 | 6 | 2| +--+-+-+---+ Obviously there are not 6 remarks, but only 2. So why is it counting 6? Is it because it's in the same table as the comments, and there are 6 comments? Does this mean MySQL counts an empty cell as having data in it? -- H | Hi, I'm currently out of my mind. Please leave a message. BP! | ~ Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 Director of Internet Operations / SysAdmin. 800.441.3873 x130 Photo Craft Laboratories, Inc. .eFax 248.671.0909 http://www.pcraft.com .3550 Arapahoe Ave, #6 .. . . . .Boulder, CO 80303, U.S.A. - 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: REPOST: SELECT syntax
On Thu, 5 Apr 2001, Ashley M. Kirchner wrote: "Thalis A. Kalfigopoulos" wrote: +--++--+-+ | task | comment |remarks | history | +--++--+-+ | task_1 | cmt_1| rmk_1| | | task_1 | cmt_2| | | | task_1 | cmt_3| |hst_1| | task_1 | cmt_4| rmk_2| | | task_1 | cmt_5| |hst_2| | task_1 | cmt_6| | | +--++--+-+ SELECT table1.task,COUNT(table2.comment),COUNT(table2.remarks),COUNT(table3.history) FROM table1 LEFT JOIN table2 ON table1.id=table2.todoid LEFT JOIN table3 ON table1.id=table3.todoid WHERE table1.id=10 GROUP BY table1.task; Based on the above data, and the above select, I get the following: +--+-+-+---+ | task | comment | remarks | history | +--+-+-+---+ | task_1 | 6 | 6 | 2| +--+-+-+---+ Obviously there are not 6 remarks, but only 2. So why is it counting 6? Is it because it's in the same table as the comments, and there are 6 comments? Does this mean MySQL counts an empty cell as having data in it? If I replicated the case correctly, I can say that it works fine for me :-/ What you mean by 'empty cell'? COUNT() doesn't include NULL values, but if the empty cells are actually empty strings '', then they'll be counted. Check you don't have empty strings as empty remarks, but that you have NULLs. Even with empty strings as remarks though you can bypass them with an extra WHERE condition :-) regards, thalis - 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
REPOST: SELECT syntax
Db - table1, table2 and table3 SELECT table1.task, table2.comment, table2.remarks, table3.history FROM table1 LEFT JOIN table2 ON table1.id=table2.todoid LEFT JOIN table3 ON table1.id=table3.todoid WHERE table1.id=10; Results (roughly) in: +--++--+-+ | task | comment |remarks | history | +--++--+-+ | task_1 | cmt_1| rmk_1| | | task_1 | cmt_2| | | | task_1 | cmt_3| |hst_1| | task_1 | cmt_4| rmk_2| | | task_1 | cmt_5| |hst_2| | task_1 | cmt_6| | | +--++--+-+ However, what I'd really like to have is the following: +--++--+-+ | task | comment |remarks | history | +--++--+-+ | task_1 | 6 | 2 | 2 | +--++--+-+ How do I change the select clause to have COUNT() in it instead? AMK4 -- W | | I haven't lost my mind; it's backed up on tape somewhere. | ~ Ashley M. Kirchner mailto:[EMAIL PROTECTED] . 303.442.6410 x130 SysAdmin / Websmith . 800.441.3873 x130 Photo Craft Laboratories, Inc. .eFax 248.671.0909 http://www.pcraft.com . 3550 Arapahoe Ave #6 .. . . . . Boulder, CO 80303, USA - 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
