Select statement with Count function on 2nd table

[enormousson]

Hi I am looking for help with an aggregate function

eg. two tables

table 1
id name
1 bob
2 rob
3 dave

table 2
id name link_id
1 smith 3
2 jones 2
3 slater 1
4 green 2
5 baker 3

I want to create a recordset that contains each of the fields in table 1 as well as the count of the number of records in table2 that match on the link id -

i.e.

(something like)
select id,
name,
COUNT(* from table2 WHERE table1.id = table2.linkid) as total
from table1, table2

returning (in this example)

id name total
1 bob 2
2 rob 2
3 dave 1

I can't seem to construct the query correctly - can anyone help ?
thanks

 

[cmmrfrds]

Try it this way.

select id,
name,
(select count(*) from table2 as B WHERE A.id = B.linkid) as total
from table1 as A

 

[1209]

try using the following query

select id, name, (select count(*) from table2 where
table2.linkid=table1.id) total from table1

Hope this is what you were looking for.

 

[enormousson]

thanks to you both - I have it sorted now

 

[moonshadow]

Select t1.id, t1.name, count(*) as total from
table1 t1 inner join table2 t2 on t1.id=t2.linkid
group by t1.id, t1.name
would be more efficient. Of course, no records will be returned if there are none matching on table2, but an outer join could be used with care to get around that.