I have 2 tables:
1) t1
mysql> describe t1;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(3) | | PRI | NULL | auto_increment |
| number | varchar(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.19 sec)
mysql> select * from t1;
+----+--------+
| id | number |
+----+--------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
+----+--------+
4 rows in set (0.20 sec)
2) t2
mysql> describe t1;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(3) | | PRI | NULL | auto_increment |
| number | varchar(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.19 sec)
mysql> select * from t2;
+----+-------+---------------------+
| id | id_t1 | t |
+----+-------+---------------------+
| 1 | 1 | 2001-07-03 15:03:34 |
| 2 | 1 | 2001-07-03 15:03:34 |
| 3 | 1 | 2001-07-03 15:03:34 |
| 4 | 2 | 2001-07-03 15:03:34 |
| 5 | 3 | 2001-07-03 15:03:34 |
| 6 | 3 | 2001-07-03 15:03:34 |
+----+-------+---------------------+
6 rows in set (0.31 sec)
I make the folowing query:
select t1.number,count(t2.id_t1) from t1 left join t2 on t1.id=t2.id_t1 where
t2.t='01-07-03 15:03:34' group by t1.number;
I see:
+--------+-----------------+
| number | count(t2.id_t1) |
+--------+-----------------+
| one | 3 |
| three | 2 |
| two | 1 |
+--------+-----------------+
What happend with column number and value "four"
What is correct query to have result below, but where clausule must stay:
+--------+-----------------+
| number | count(t2.id_t1) |
+--------+-----------------+
| one | 3 |
| three | 2 |
| two | 1 |
| four | 0 |
+--------+-----------------+
PLEASE HELP.
Regards:
Ireneusz Piasecki
webmaster www.b-c.pl
KomNet s.c
[EMAIL PROTECTED]
tel: 0 606 356 235
[EMAIL PROTECTED]
