Help for a query in SQL

Could anybody help me with this crazy query?

[beto_x]

I Have next 2 tables in Access:

Table 1 Fields:
+ idsubject (key)
+ another fields....

Table 2 Fields:
+ idfollow (key)
+ idsubject (relation with Table 1)
+ another fields....

One,Two or more idfollow's in Table 2 could have the same idsubject and I want to show a query where I could see each idsubject and how many idfollow's has. If an idsubject do not have any idfollow I want to show "0".

Is there any good person, please :)?

[coopster]

Welcome to WebmasterWorld, beto_x!

How about a LEFT JOIN?

SELECT 
 table1.idsubject, 
 table2.idfollow 
 FROM table1 
 LEFT JOIN table2 
 ON (table1.idsubject=table2.idsubject) 
 ORDER BY table1.idsubject, table2.idfollow 
;

This would return all the rows in table1 and any related rows in table2. If there were no related rows in table2, then table2.idfollow would be

NULL

(not zero as you asked). Would this work for you?

[IanTurner]

select table1.idsubject,count(*) from
table1 join table2
on table1.idsubject = table2.idsubject
group by table1.idsubject

union

Select table1.idsubject,0
From table1
Where table1.idsubject NOT IN (Select Distinct idsubject from table2)

Thats how to do it in SQL whether ACCESS supports all these statements is another matter!

[IanTurner]

select table1.idsubject,count(table2.idfollow) from
table1 left join table2
on table1.idsubject = table2.idsubject
group by table1.idsubject

This may be simpler.

[coopster]

Thanks, IanTurner, I misunderstood the "how many idfollow's has" part of it. Seems a

COUNT

is necessary.

[IanTurner]

coopster your left join allowed me to simplify the statement into something that might work in Access.