select t1.id_t1, t1.ft1, count(t2.*)
from t1 left join t2 on t2.fk_t1_id = t1.id_t1
group by t1.id_t1, t1.ft1

... or you can simplify that to ...

select id_t1, ft1, count(*)
from t1 left join t2 on t2.fk_t1_id = t1.id_t1
group by id_t1, ft1

-Philip

---------- Original Message -----------
From: Namýk Kemal KARASU <nkkarasu@...>
To: <firebird-support@yahoogroups.com>
Sent: Wed, 20 Jul 2005 22:28:54 +0300
Subject: [firebird-support] A simple Question :)

> Here are the tables:
>
> **************************************
>
> t1 | t2
>
> ------------ | ----------------------
>
> id_t1 ft1 | id_t2 fk_t1_id ft2
>
> ----- --- | ----- -------- ---
>
> 1 A | 1 1 X
>
> 2 B | 2 2 Y
>
> 3 C | 3 2 Z
>
> | 4 1 T
>
> | 5 1 P
>
> ***************************************
>
> I want get count of related fields in t2 with t1, result set should be like
> this:
>
> *******************************
>
> Res
>
> -------------------------------
>
> id_t1 ft1 count_of_fk_t1_id
>
> 1 A 3
>
> 2 B 2
>
> 3 C 0 (or null)
>
> ********************************
>
> I do that by using a view:
>
> *******************************************
>
> CREATE VIEW vw_count(fk_t1_id, count_of_fk)
>
> AS
>
> select fk_t1_id, count(*) as count_of_fk
>
> from t2
>
> group by count_of_fk
>
> ...
>
> select id_t1, ft1, count_of_fk
>
> from t1
>
> inner join vw_count on id_t1 = fk_t1_id
>
> *******************************************
>
> My I do that without using secondary objects?
>
> [Non-text portions of this message have been removed]
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>

------- End of Original Message -------