Help with SQL 2005 and inner join

Question

I am trying to inner join 2 temp tables
I know this can be done, I have done this before but i completely forgot how to do it

Please advise me
Below is the query that I try to execute

select tmp1.*, tmp2.cnt from
(
    select 
     1 as ClassificationType,
     tblMatches.IdGame,
     tblMatches.IdPlayer,
     sum(Score) as Score, sum(Points) as Points, sum(OpponentScore) as OpponentScore,
     count(ID) as MatchesCount, count(distinct IdCompetition) as CompetitionsCount
    from 
     tblMatches 
    group by IdPlayer, IdGame 
) as tmp1
inner join (select IdWinner, count(IdWinner) as cnt from tblCompetitions where IdWinner = tmp1.IdPlayer) as tmp2 
      on tmp2.IdWinner = tmp1.IdPlayer

This will fail with
I think I am not allowed to use tmp1 in the subquery that create tmp2

Msg 4104, Level 16, State 1, Line 17 The multi-part identifier "tmp1.IdPlayer" could not be bound.

Answer 1

You are not trying to join two temp tables, but two derived tables.

You cannot access the inner data of one derived table in outside of it unless it's in the SELECT clause.

Try the following:

select tmp1.*, tmp2.cnt from
(
    select 
        1 as ClassificationType,
        tblMatches.IdGame,
        tblMatches.IdPlayer,
        sum(Score) as Score, sum(Points) as Points, sum(OpponentScore) as OpponentScore,
        count(ID) as MatchesCount, count(distinct IdCompetition) as CompetitionsCount
    from 
        tblMatches      
    group by IdPlayer, IdGame   
) as tmp1
inner join (select IdWinner, count(IdWinner) as cnt from tblCompetitions GROUP BY IdWinner) as tmp2 
                on tmp2.IdWinner = tmp1.IdPlayer

Answer 2

The where clause in tmp2 duplicates the join condition:

inner join (select IdWinner, count(IdWinner) as cnt 
            from tblCompetitions 
            where IdWinner = tmp1.IdPlayer) as tmp2 
on         tmp2.IdWinner = tmp1.IdPlayer

Just remove the where clause. In addition, like Astander noted in his now deleted post, the second query needs a group by too:

inner join (select IdWinner, count(IdWinner) as cnt 
            from tblCompetitions
            group by IdWinner) as tmp2 
on         tmp2.IdWinner = tmp1.IdPlayer

The reason you can't reference the outer query from a subquery is that this would make the right part of the join depend on the left part of the join.

Answer 3

You shouldn't actually need the second subquery. What about this?

SELECT tmp1.ClassificationType, tmp1.IdGame, tmp1.IdPlayer,
       COUNT(tblCompletions.IdWinner) as cnt FROM
(
    SELECT 
        1 as ClassificationType,
        tblMatches.IdGame,
        tblMatches.IdPlayer,
        sum(Score) as Score, sum(Points) as Points, sum(OpponentScore) as OpponentScore,
        count(ID) as MatchesCount, count(distinct IdCompetition) as CompetitionsCount
    FROM 
        tblMatches      
    GROUP BY IdPlayer, IdGame   
) as tmp1
INNER JOIN tblCompletions ON tmp1.IdPlayer = tblCompletions.IdWinner
GROUP BY tmp1.ClassificationType, tmp1.IdGame, tmp1.IdPlayer

Answer 4

select
    1 as ClassificationType,
    tmp1.IdGame,
    tmp1.IdPlayer,
    sum(tmp1.Score) as Score,
    sum(tmp1.Points) as Points,
    sum(tmp1.OpponentScore) as OpponentScore,
    count(tmp1.ID) as MatchesCount,
    count(distinct tmp1.IdCompetition) as CompetitionsCount,
    count(tmp2.IdWinner) as cnt
from 
    tblMatches tmp1
    inner join
    tblCompetitions tmp2
        on tmp2.IdWinner = tmp1.IdPlayer
group by
    tmp1.IdPlayer,
    tmp1.IdGame