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.

Expand|Select|Wrap|Line Numbers

 SELECT     TBL_PRACTICE.Practice_ID, TBL_PRACTICE.Practice_Name,

    (SELECT count(TBL_BRANCH.Branch_ID) FROM TBL_BRANCH) AS Branches

FROM TBL_PRACTICE INNER JOIN

    TBL_BRANCH ON TBL_PRACTICE.Practice_ID = TBL_BRANCH.Practice_ID

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

Expand|Select|Wrap|Line Numbers

 SELECT TBL_PRACTICE.Practice_ID, TBL_PRACTICE.Practice_Name, COUNT(TBL_BRANCH.Branch_ID) AS Branches

FROM TBL_PRACTICE INNER JOIN

TBL_BRANCH ON TBL_PRACTICE.Practice_ID = TBL_BRANCH.Practice_ID

GROUP BY Practice_Name

Counting with an Inner Join.

re: Counting with an Inner Join.

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about