Proper use of Inner Join with nested select?

ok, i am a novice w/ sql queries, so this will probably be cake for
most of you if i can explain it properly.
I am trying to run a query against 2 tables, tbPlayers and tbResults
that are joined one to many by a PlayerId field. This query is used to
retrieve standings of poker tournament results, 1 record for each
player, an sum of money won from all tournaments, and a count of how
many times they have won any amount of money from a tournament.
SELECT tbPlayers.Name,
Sum(tbResults.MoneyWon) as [Prize Money]
(Select Count(MoneyWon) FROM tbResults WHERE MoneyWon > 0) as Cashes
FROM tbPlayers
INNER JOIN tbResults on tbResults.PlayerId = tbPlayers.Id
GROUP BY Players.Name
This query runs, but the value retrieved for 'Cashes' is incorrect, as
it brings back the count of ALL records in the table instead of just
those associated with a singe PlayerId.
Any thoughts would be greatly appreciated! (and i'll happily offer up a
free version of my Poker Tournament Director application once its ready
for beta ... which is soon!)Try something like this:
declare @.Player table (PlayerId int, name varchar(20))
insert @.Player values (1, 'jeff')
insert @.Player values (2, 'ed')
declare @.Results table (PlayerId int, MoneyWon int )
insert @.Results values(1, 100)
insert @.Results values(1, 0)
insert @.Results values(1, 1000)
insert @.Results values(2, -100)
insert @.Results values(2, 50)
SELECT p.Name,
Sum(r.MoneyWon) as [Prize Money],
Sum(case when MoneyWon > 0 then 1 else 0 end) as Cashes
FROM @.Player p
INNER JOIN @.Results r on p.PlayerId = r.PlayerId
GROUP BY p.Name|||Perfect!! Thank you - you the man!|||BriskDuck@.gmail.com wrote:
> ok, i am a novice w/ sql queries, so this will probably be cake for
> most of you if i can explain it properly.
> I am trying to run a query against 2 tables, tbPlayers and tbResults
> that are joined one to many by a PlayerId field. This query is used to
> retrieve standings of poker tournament results, 1 record for each
> player, an sum of money won from all tournaments, and a count of how
> many times they have won any amount of money from a tournament.
> SELECT tbPlayers.Name,
> Sum(tbResults.MoneyWon) as [Prize Money]
> (Select Count(MoneyWon) FROM tbResults WHERE MoneyWon > 0) as Cashes
> FROM tbPlayers
> INNER JOIN tbResults on tbResults.PlayerId = tbPlayers.Id
> GROUP BY Players.Name
> --
> This query runs, but the value retrieved for 'Cashes' is incorrect, as
> it brings back the count of ALL records in the table instead of just
> those associated with a singe PlayerId.
> Any thoughts would be greatly appreciated! (and i'll happily offer up a
> free version of my Poker Tournament Director application once its ready
> for beta ... which is soon!)
>
Try this :
SELECT tbPlayers.Name,
Sum(tbResults.MoneyWon) as [Prize Money],cashes.[WinCount]
FROM tbPlayers
INNER JOIN tbResults on tbResults.PlayerId = tbPlayers.Id
INNER JOIN (SELECT [PlayerId],COUNT([MoneyWon]) AS [WinCount] FROM
tbResults GROUP BY [PlayerId]) AS Cashes ON tbResults.[PlayerId] =
cashes.[PlayerId]
GROUP BY tbPlayers.Id,tbPlayers.[Name],cashes.WinCount
-JayDial|||JeffB wrote:
> Try something like this:
>
> declare @.Player table (PlayerId int, name varchar(20))
> insert @.Player values (1, 'jeff')
> insert @.Player values (2, 'ed')
> declare @.Results table (PlayerId int, MoneyWon int )
> insert @.Results values(1, 100)
> insert @.Results values(1, 0)
> insert @.Results values(1, 1000)
> insert @.Results values(2, -100)
> insert @.Results values(2, 50)
> SELECT p.Name,
> Sum(r.MoneyWon) as [Prize Money],
> Sum(case when MoneyWon > 0 then 1 else 0 end) as Cashes
> FROM @.Player p
> INNER JOIN @.Results r on p.PlayerId = r.PlayerId
> GROUP BY p.Name
>
Whoa nevermind, forget mine. This looks much better! ;)sql