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
_________________________________________________________________
Go down memory lane. Revisit the cool times. http://www.batchmates.com/msn.asp Reconnect with old friends!
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
