Multi-Table Selecting with Count of Foreign Key Referred Rows

Question

I've tables related with foreign keys and i try to prepare a view to compose them via inner joins on SQL Server. I don't know if using inner join's is the way, but I can't get what I want anyway. Tables are like below (I have more than 2 tables): Table1: ``` | ID | Bla Bla... | ``` Table2: ``` | ID | Table1ID | Bla Bla... | ``` The query I tried is like this: ``` Select Table1.ID, COUNT(Table2.ID) as FooCount From Table1 Inner Join Table2 on Table2.Table1ID = Table1.ID ``` The result I want to see should be this: ``` | ID | FooCount | ----------------------- | 1 | 45 | | 2 | 75 | | 3 | 98 | | 4 | 100 | | 5 | 11 | | 6 | 37 | ``` How can I do this?

Accepted Answer

You don't even need a join to do this:

```
SELECT Table1Id AS ID, COUNT(*) as FooCount FROM Table2 GROUP BY Table1Id
```

Multi-Table Selecting with Count of Foreign Key Referred Rows

Solution

Problem