3 table query

[xMetalDetectorx]

Hi Everyone!
I have three tables which i need to query. Here is their format:

Team
TeamID
TeamName

CoachTeam
CoachID
TeamID

PlayerTeam
PlayerID
TeamID

I need to get a list of Teams for a Coach, and that list needs to have a column for Number of Players on that team.
I can get a list of teams of a coach, and i can get the number of players for a team individually, but I don't know how to combine those two queries to give me 1 table.
What would be the best way to achieve this? Thanks in advance!

[gpl]

What do your 2 queries look like ?

[NeoPa]

I'd start with a CTE (Common Table Expression) for the subquery (See Subqueries in SQL) which counts the players in the team, then, when you have three record sources that all work at the same level simply join them together :

WITH cteNumPlayers AS
(
    SELECT [TeamID]
         , COUNT(*) AS [NumPlayers]
    FROM [PlayerTeam]
)
SELECT   tCT.CoachID
       , tT.TeamName
       , cNP.NumPlayers
FROM    ([CoachTeam] AS [tCT]
         INNER JOIN
         [Team] AS tT
  ON     tCT.TeamID = tT.TeamID)
         LEFT JOIN
         [cteNumPlayers] AS cNP
  ON     tCT.TeamID = cNP.TeamID
ORDER BY tCT.CoachID
       , tCT.TeamID

You may well have a [Coach] table too if I guess aright, but fitting that in should be simple enough. Ask if you need help though.

[xMetalDetectorx]

@NeoPa

The first part of that SQL did not execute.
# WITH cteNumPlayers AS
# (
# SELECT [TeamID]
# , COUNT(*) AS [NumPlayers]
# FROM [PlayerTeam]
# )

Here is the original table PlayerTeam

PlayerTeam
TeamID - UserID
1 - 22
1 - 11
1 - 10
2 - 15

And here is how it should be pivoted

PlayerTeamPivot ed
TeamID NumPlayers
1 - 3
2 - 1

I read up on the PIVOT command in MSDN, but am thoroughly confused on how to use it. I know the aggregate function i need is COUNT, but what do i put after IN for the pivot sql?

[ck9663]

You can not run the WITH part with running the second select. It should be executed both.

You may want to also try this:

select c.CoachId, p.TeamId, count(*)
from PlayerTeam p
inner join CoachTeam c on p.TeamId = c.TeamId
group by c.CoachId, p.TeamId

Happy Coding!!!

~~ CK

[xMetalDetectorx]

Alrite, so here is my first query:

SELECT tblCoachTeam.UserID, tblTeam.TeamID
FROM tblCoachTeam
INNER JOIN tblTeam ON tblCoachTeam.TeamID = tblTeam.TeamID
WHERE tblCoachTeam.UserID = @UserID

This gives me the list of teams for a coach.

The second part of the query is:

SELECT TeamID, COUNT(UserID) AS [NumPlayers]
    FROM dbo.tblPlayerTeam
    GROUP BY TeamID

This gives me the number of players on each team.

Now I have joined them with this:

WITH cteNumPlayers AS
(
    SELECT TeamID, COUNT(UserID) AS [NumPlayers]
    FROM dbo.tblPlayerTeam
    GROUP BY TeamID
)
SELECT tblCoachTeam.UserID, tblTeam.TeamID, cteNumPlayers.NumPlayers
FROM tblCoachTeam
INNER JOIN tblTeam ON tblCoachTeam.TeamID = tblTeam.TeamID
LEFT JOIN cteNumPlayers ON tblCoachTeam.TeamID = cteNumPlayers.TeamID
WHERE tblCoachTeam.UserID = @CoachID

It works!

Thanks for your help everyone!!!

[NeoPa]

It seems I missed out the GROUP BY clause of the CTE. As CK says, the CTE is a definition (referred to later by the name - cteNumPlayers). This can be considered as very similar to a subquery, but instead of being defined inline, as a subquery is, it is declared up front, and referred to within the main SQL by name. I've included the updated version below :

WITH cteNumPlayers AS
(
    SELECT   [TeamID]
           , COUNT(*) AS [NumPlayers]
    FROM     [PlayerTeam]
    GROUP BY [TeamID]
)
SELECT   tCT.CoachID
       , tT.TeamName
       , cNP.NumPlayers
FROM    ([CoachTeam] AS [tCT]
         INNER JOIN
         [Team] AS tT
  ON     tCT.TeamID = tT.TeamID)
         LEFT JOIN
         [cteNumPlayers] AS cNP
  ON     tCT.TeamID = cNP.TeamID
ORDER BY tCT.CoachID
       , tCT.TeamID

All that said, of course, CK may have a point with his simpler overall GROUP BY offering. It only uses two of the three tables, but I suspect it could be got to work as required. I couldn't think how when I first looked at the problem, but maybe I just overlooked the simple (as I have a habit of doing sometimes :D)

Anyway, as a last point, we don't normally allow members to select their own posts as best answer. Exceptions might be if they went off, without help from other members, and researched and posted their solutions. This clearly isn't the case here, so apologies, but I had to reset that.

[xMetalDetectorx]

@NeoPa

Thanks for your reply!

regarding last point:
I was not sure which post i should mark as "best answer", since they all added a little bit to the solution. So I just posted the complete solution and made sure to thank everyone for their efforts. =)

I guess I will choose yours now since you have corrected it. Thanks!

[NeoPa]

You make a good point, but as these are also used (main point really is to help a browser to go straight to a good answer of course) to rate members for how useful their answers are, then we like to avoid the situation where OPs get their help then flag their own posts as Best Answer (even though it may be the one where all the points are pulled together as it were).