"Asish Samanta" <[EMAIL PROTECTED]> wrote: > > When I run the sql query : > > select a.group_id as "Select", a.group_name as "Group Name", > date_format(a.group_created_date,"%M %e, %Y %H:%i") as "Created Date", > date_format(a.last_modification_date,"%M %e, %Y %H:%i") as "Last Modified", > a.learning_style as "Style" , > count(c.group_id) as "No. of Members" from student_group a left join > student_group_association c on a.group_id = c.group_id > where a.group_id = 'FIN' group by c.group_id, a.group_name, a.group_id union > select a.group_id as "Select", a.group_name as "Group Name", > date_format(a.group_created_date,"%M %e, %Y %H:%i") as "Created Date", > date_format(a.last_modification_date,"%M %e, %Y %H:%i") as "Last Modified", > a.learning_style as "Style" , > count(c.group_id) as "No. of Members" from student_group a left join > student_group_association c > on a.group_id = c.group_id > where a.group_id <> 'FIN' group by c.group_id, a.group_name, a.group_id; > > the buggy result will come: > > +--------+-------------+--------------------+--------------------+----------+----------------+ > | Select | Group Name | Created Date | Last Modified | Style > | No. of Members | > +--------+-------------+--------------------+--------------------+----------+----------------+ > | FIN | Finance | June 6, 2003 19:32 | June 6, 2003 19:32 | Advanced > | 1 | > | DEV | Development | June 6, 2003 19:38 | June 6, 2003 19:38 | Expert > | 0 | > +--------+-------------+--------------------+--------------------+----------+----------------+ > 2 rows in set (0.00 sec). > > Actually in the count column will be 0 but in first row it shows 1. > > If I ommit the "Union" clause, it shows the result properly: > > select a.group_id as "Select", a.group_name as "Group Name", > date_format(a.group_created_date,"%M %e, %Y %H:%i") as "Created Date", > date_format(a.last_modification_date,"%M %e, %Y %H:%i") as "Last Modified", > a.learning_style as "Style" , > count(c.group_id) as "No. of Members" from student_group a left join > student_group_association c on a.group_id = c.group_id > where a.group_id = 'FIN' group by c.group_id, a.group_name, a.group_id; > > +--------+------------+--------------------+--------------------+----------+----------------+ > | Select | Group Name | Created Date | Last Modified | Style | > No. of Members | > +--------+------------+--------------------+--------------------+----------+----------------+ > | FIN | Finance | June 6, 2003 19:32 | June 6, 2003 19:32 | Advanced | > 0 | > +--------+------------+--------------------+--------------------+----------+----------------+ > 1 row in set (0.00 sec) > > and > > +--------+-------------+--------------------+--------------------+--------+----------------+ > | Select | Group Name | Created Date | Last Modified | Style | > No. of Members | > +--------+-------------+--------------------+--------------------+--------+----------------+ > | DEV | Development | June 6, 2003 19:38 | June 6, 2003 19:38 | Expert | > 0 | > +--------+-------------+--------------------+--------------------+--------+----------------+ > 1 row in set (0.00 sec) > > I am usin MySQL 4.0.12 on Windows NT 4.0
Could you provide table structures and some data for testing? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
