---- Original Message -----
From: "Robert Vetter" <[EMAIL PROTECTED]>
To: "Ireneusz Piasecki" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, July 05, 2001 4:26 PM
Subject: Re: three tables and count() - Urgent i need it to my job !!
>
>
> Ireneusz Piasecki wrote:
> >
> > 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
>
> Hello,
>
> I don't know what exactly you want to count, but one thing seems to be
> obviously to me: You can't make a left join between t2 and t3 on
> t3.id_t1=t1.id (line 3 of the query).
>
> Pozdrowienia do Polski,
>
> Robert Vetter
>
Hi, it's me again.
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
I would like to have this result from above query:
this result is correct (i hope)
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 |
With above result i now how many records is in t2 for every records from t1
and how many
is in t3 for every records from t1.
But, i don't konw, how can i bulid this correct query, to have correct
result.
Thansk
Regards
Ireneusz Piasecki
---------------------------------------------------------------------
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
