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]