Just to point out, you did not describe t2 in this email, but you have
redescribed t1 again, and not table t2.
Also, from looking at your query, I can see the following things: If you
did not have the where condition checking for the timestamp, then you would
get the entry for "four" since there would be no condition to filter it
out. You have placed the where clause on a t2 field however, and in the
case of the extra "empty" row for the "four" value which would be added by
the left join operation, it does NOT match the where clause (since it has
no timestamp value), and therefore it is not included in the query. You
need to change the where clause to return true for any rows that do not
exist in t2 and which have no timestamp. If, instread you modify the where
clause in your query to say:
where t2.t='2001-07-03 15:03:43' OR t2.t IS NULL
I believe you're query would work.
At 04:49 PM 7/3/01 +0200, Ireneusz Piasecki wrote:
>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]
---------------------------------------------------------------------
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
