problem for build a query

[Rupesh Kumar Swami]

hi all, i want to build a query but not succesful i have two table named A and B A table has field id(N), title(Varchar) B table has field mid(N), id(N) so i want to id, title from A table and count of id from B table let me explain with data Table A data

1 title1
2 title2
3 title3

Table B data

1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 3
9 3

so i want to following result

id title count
1 title1 4
2 title2 3
3 title3 2

Please suggest how can build this query?

rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

[Dimitri Witkowski]

Here's the idea how to do this:

SELECT A.id, A.title, COUNT(*) as cnt
FROM A left join B on A.id = B.id
GROUP BY A.id, A.title

See my article about Windows 7 Taskbar timer here on CodeProject

[i j russell]

SELECT A.id, A.title, S.total
FROM A
LEFT OUTER JOIN
(
SELECT id, COUNT(id) as total
FROM B
GROUP BY id
) S on S.id = A.id
ORDER BY A.id ASC