list with count of matching items (2 tbls)

[idimmu]

hello!

Tbl1
id
name

Tbl1 ROWS
id    name
1     name1
2     name2
3     name3


Tbl2
id
tbl1_id

Tbl2 ROWS
id    tbl1_id
1     1
2     1
3     2

I have 2 tables with a foreign key in Tbl2. I'm looking for the sql to list all rows in Tbl1 and count how many rows match the Tbl1.id in Tbl2 and display it in the row.
I want the out put to read:
id name count
1 name1 2
2 name2 1
3 name3 0

Is this possible? Thanks in advance for your help!

[mikr0s]

SELECT a.id, a.name, COUNT(b.tbl1_id) AS count
FROM Tbl1 a LEFT JOIN Tbl2 b ON a.id = b.tbl1_id
GROUP BY a.id, a.name

--HTH--

[idimmu]

Thank you for your response, but since I'm new to this I don't understand the meaning of "a" and "b"...
Is a.id supposed to mean tbl1.id?

[dararamu]

Thank you for your response, but since I'm new to this I don't understand the meaning of "a" and "b"...
Is a.id supposed to mean tbl1.id?

a and b are alias names for tbl1 and tbl2
a.id and tbl1.id both are same
alias names are useful when the table name is very long
instead of type long table name you can use alias name for table


by using table alias name you can join the table to same table
means self join

[idimmu]

Thank you for the explaination! I can see where that would be helpful!