SQL SELECT. Involving COUNT

Question

So I have 3 tables. One with the Id and Name, the second one with Id and some other stuff which is not relevant in here and the third table with it's own Id, table1Id and table2Id (table1 and table2 does not know anything about each other). I want to SELECT the following. Id and Name from the table1 and the count of table2Ids from table3 which would be grouped by table1Id. How can I do that. I'm pretty new to SQL so any help would be appreciated. thanks in advance. If needed I can provide more details.

Answer 1

create table table1(id int, name nvarchar(20))

insert into table1 values(1, 'Value 1')
insert into table1 values(2, 'Value 2')
insert into table1 values(3, 'Value 3')

create table test1(id int, table1id int, table2id int)

insert into test1 values(1,1,1)
insert into test1 values(2,1,2)
insert into test1 values(3,1,3)
insert into test1 values(4,1,4)
insert into test1 values(5,1,5)
insert into test1 values(6,2,1)
insert into test1 values(7,2,2)
insert into test1 values(8,2,3)
insert into test1 values(9,2,4)
insert into test1 values(10,2,5)
insert into test1 values(11,3,1)
insert into test1 values(12,3,2)
insert into test1 values(13,3,3)
insert into test1 values(14,3,4)
insert into test1 values(15,3,5)


select n.id, n.name, count(distinct(table2id)) [Count]
from test1 t
inner join table1 n on (t.table1id = n.id)
group by n.id, n.name
order by 1

Answer 2

SELECT
    Table1.ID,
    Table1.Name,
    COUNT(Table3.Table2ID) AS Table2IDCount
FROM
    Table1
    LEFT JOIN Table3 ON Table1.ID = Table3.Table1ID
GROUP BY
    Table1.ID,
    Table1.Name