Printable View
hello!
Code: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!
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--
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?
Quote:
Originally Posted by idimmu
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
:)
Thank you for the explaination! I can see where that would be helpful!