Problem with Join and Count in the same query

[shauns1]

Hi

I have the following query:

SELECT ft.FantasyTeamID, ft.TeamName, fp.FantasyTeamPlayerID, p.PlayerName, p.PositionID
FROM tblFantasyTeams AS ft, tblFantasyTeamPlayers AS fp, tblPlayers AS p, tblGoals as g
WHERE p.PlayerID = fp.PlayerID AND fp.FantasyTeamID = ft.FantasyTeamID AND ft.FantasyTeamID = @FantasyTeamID
ORDER BY fp.CreationID;

Which gives me a list of fp PlayerID's along with their corresponding Name and Position for a given ft ID. This is correct. But what I would also like to add to this query is a count of all goals in tblGoals for each PlayerID.

I thought the following query should work but when I try it just gives me one record, rather than the list I got before. I think I'm going wrong with the join somewhere. (changes to the above query are in bold below)

SELECT ft.FantasyTeamID, ft.TeamName, fp.FantasyTeamPlayerID, p.PlayerName, p.PositionID,Count(g.GoalID)
FROM tblFantasyTeams AS ft, tblFantasyTeamPlayers AS fp, tblPlayers AS p, tblGoals as g
WHERE p.PlayerID = fp.PlayerID AND fp.FantasyTeamID = ft.FantasyTeamID AND ft.FantasyTeamID = @FantasyTeamID AND g.PlayerID = fp.PlayerID
GROUP BY fp.FantasyTeamPlayerID, ft.FantasyTeamID, ft.TeamName, p.PlayerName, p.PositionID,fp.CreationID
ORDER BY fp.CreationID;




Many thanks for any ideas

P.S Im hoping you don't need to see the structure of all the tables as all the relevant info is contained in the queries above.

Thanks again.

Shaun

 

[lespaul]

This may:

SELECT ft.FantasyTeamID, ft.TeamName, fp.FantasyTeamPlayerID, p.PlayerName, p.PositionID, Count(g.PlayerID)
FROM tblFantasyTeams AS ft
INNER JOIN tblFantasyTeamPlayers AS fp ON ft.FantasyTeamID = fp.FantasyTeamID
INNER JOIN tblPlayers AS p on ft.PlayerID = p.PlayerID
INNER JOIN tblGoals as g on p.PlayerID = g.PlayerID
WHERE ft.FantasyTeamID = @FantasyTeamID
GROUP BY ft.FantasyTeamID, ft.TeamName, fp.FantasyTeamPlayerID, p.PlayerName, p.PositionID
ORDER BY fp.CreationID;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Golom]

It may be that players are being excluded because they have no goals so there are no records for them in tblGoals. Try something like

SELECT ft.FantasyTeamID, 
       ft.TeamName, 
       fp.FantasyTeamPlayerID, 
        p.PlayerName, 
        p.PositionID,
       NZ(Select Count(*) From tblGoals g 
          Where g.PlayerID = fp.PlayerID)) As [Goals Scored]

FROM tblFantasyTeams AS ft, 
     tblFantasyTeamPlayers AS fp, 
     tblPlayers AS p

WHERE p.PlayerID = fp.PlayerID 
  AND fp.FantasyTeamID = ft.FantasyTeamID 
  AND ft.FantasyTeamID = @FantasyTeamID 

ORDER BY fp.CreationID;

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[r937]

add fp.CreationID to the SELECT

your joins look okay



r937.com | rudy.ca

 

[shauns1]

Golom - job done. Thank you.

Can I just ask where 'NZ' comes from. I get lost in the query at that point. Is it just a variable name?

Plus it helps so mucg when you layout the query this way. I didn't realise access allowed this.

Many thanks

 

[r937]

you don't actually need the NZ because the COUNT subquery will always return a value

you can achieve the same results as Golom's query with a LEFT OUTER JOIN

SELECT ft.FantasyTeamID
     , ft.TeamName
     , fp.FantasyTeamPlayerID
     , p.PlayerName
     , p.PositionID
     , fp.CreationID
     , Count(g.GoalID) as goals
  FROM ((
       tblFantasyTeams AS ft
inner
  join tblFantasyTeamPlayers AS fp
    on fp.FantasyTeamID = ft.FantasyTeamID
       )
inner
  join tblPlayers AS p
    on p.PlayerID = fp.PlayerID
       )
[b]left outer[/b]
  join tblGoals as g
    on g.PlayerID = fp.PlayerID 
 WHERE ft.FantasyTeamID = @FantasyTeamID 
GROUP 
    BY fp.FantasyTeamPlayerID
     , ft.FantasyTeamID
     , ft.TeamName
     , p.PlayerName
     , p.PositionID
     , fp.CreationID
ORDER 
    BY fp.CreationID;

r937.com | rudy.ca

 

[shauns1]

I see. But was 'NZ' just a variable name?

 

[r937]

NZ() is a function which returns 0 whenever its argument is null

its argument in this case was a subquery

but it is not needed since the subquery cannot return null

r937.com | rudy.ca

 

[shauns1]

Oh right, I see.

Thanks for the clarification.