Hi my friends !!
Some few days i have problems with count().
i have 3 tables:
t1 holds:
+----+--------+
| id | number |
+----+--------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
| 5 | five |
| 6 | sixs |
+----+--------+
t2 holds:
+----+-------+---------------------+
| 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-04 12:00:00 |
| 6 | 3 | 2001-07-04 12:00:00 |
| 7 | 4 | 2001-07-04 13:11:00 |
+----+-------+---------------------+
t3 have it:
+----+-------+---------------------+
| id | id_t1 | t |
+----+-------+---------------------+
| 1 | 1 | 2001-07-04 13:11:00 |
| 2 | 2 | 2001-07-04 13:11:00 |
| 3 | 3 | 2001-07-04 13:11:00 |
| 4 | 1 | 2001-07-04 13:11:00 |
| 5 | 4 | 2001-07-04 13:11:00 |
| 6 | 4 | 2001-07-04 13:11:00 |
| 7 | 4 | 2001-07-04 13:11:00 |
+----+------+----------------------+
so, my target is: how many rows there are in table t2 and t3 coresponding to t1.
Column id_t1 in t2 and t3 are values that are present in t1.id. It is important, it
must be one query to database.
I make this query:
select t1.id,number,count(t2.id_t1),count(t3.id_t1) from t1
left join t2 on t2.id_t1=t1.id
left join t3 on t3.id_t1=t1.id
group by number order by number desc
But the result is not correct.
What shuld to be a query to get right result ???
i would like to have this (it is correct):
id | number | count(t2_id1) | count(t3_id1)|
1 | one | 3 | 2 |
2 | two | 1 | 1 |
3 | three | 2 | 1 |
4 | four | 1 | 3 |
5 | five | 0 | 0 |
6 | six | 0 | 0 |
Does anybody help me ????
p.s; mayby that is impossible ??
Ireneusz Piasecki
webmaster www.b-c.pl
KomNet s.c
[EMAIL PROTECTED]
tel: 0 606 356 235
[EMAIL PROTECTED]
