Counting with an Inner Join.

[new2sql]

I have two tables.

TBL_PRACTICE
Practice_ID (primary key)
Practice_Name
etc.

TBL_BRANCH
Branch_ID (primary key)
Branch_Name
Practice_ID
etc.

I need to create a view that has 2 fields. Practice Name, Number of Branches.

I have the following SQL, but this counts all the branches, not just the branches for this particular practice.

[CODE=mysql]SELECT TBL_PRACTICE.Pr actice_ID, TBL_PRACTICE.Pr actice_Name,
(SELECT count(TBL_BRANC H.Branch_ID) FROM TBL_BRANCH) AS Branches
FROM TBL_PRACTICE INNER JOIN
TBL_BRANCH ON TBL_PRACTICE.Pr actice_ID = TBL_BRANCH.Prac tice_ID[/code]

If someone could let me know how I count only the branches for the corresponding Practice.

Thanks in advance, much appriciated.

[mwasif]

Use COUNT() and GROUP BY
[CODE=mysql]SELECT TBL_PRACTICE.Pr actice_ID, TBL_PRACTICE.Pr actice_Name, COUNT(TBL_BRANC H.Branch_ID) AS Branches
FROM TBL_PRACTICE INNER JOIN
TBL_BRANCH ON TBL_PRACTICE.Pr actice_ID = TBL_BRANCH.Prac tice_ID
GROUP BY Practice_Name[/code]