The multi-part identifier could not be bound - SQL Server 2014

Question

I am trying to solve the error in the below query

Yes I have checked many same questions but still can not figure out the solution

The error:

The multi-part identifier "Table_2.id" could not be bound.

When I remove the inner join the query runs perfectly fine

I have to solve this problem without turning it into explicit joins because i have so many dynamic filtering which add and x=y clauses to the end of the query

Thank you

SELECT TOP 10 username, 
              NAME, 
              table_1.authoritylevel, 
              totalcount, 
              avglevel, 
              table_2.pokemonid, 
              pokemonlevel, 
              table_2.id, 
              pokemonexp, 
              battlecount, 
              battlevictorycount, 
              table_1.userid 
FROM   table_1, 
       table_2, 
       table_3, 
       table_4 
       LEFT OUTER JOIN (SELECT Count(table_5.offereruserid) AS OfferCount, 
                               table_5.offereduserspokemonsid 
                        FROM   table_5 
                        GROUP  BY offereduserspokemonsid) innerQuestion 
                    ON innerQuestion.offereduserspokemonsid = table_2.id 
WHERE  table_3.pokemonid = table_2.pokemonid 
       AND pokemonplace = 'trade' 
       AND table_4.pokemonid = table_2.pokemonid 
       AND table_2.userid = table_1.userid 
       AND table_2.userid != 1 

Answer 1

If you are keen on keeping the implicit joins, you could split your query into several result sets using WITH. According to this article, you can no longer do "implicit outer joins." Give this a try:

WITH OfferCounts as
(
    SELECT Count(table_5.offereruserid) AS OfferCount,  table_5.offereduserspokemonsid 
    FROM table_5 
    GROUP  BY offereduserspokemonsid
),
EverythingElse AS
(
    SELECT TOP 10 username, 
                  NAME, 
                  table_1.authoritylevel, 
                  totalcount, 
                  avglevel, 
                  table_2.pokemonid, 
                  pokemonlevel, 
                  table_2.id, 
                  pokemonexp, 
                  battlecount, 
                  battlevictorycount, 
                  table_1.userid 
    FROM   table_1, 
           table_2, 
           table_3, 
           table_4,
    WHERE  table_3.pokemonid = table_2.pokemonid 
           AND pokemonplace = 'trade' 
           AND table_4.pokemonid = table_2.pokemonid 
           AND table_2.userid = table_1.userid 
           AND table_2.userid != 1 

)
Select * 
From EverythingElse t1
    left join OfferCounts t2 on t1.offereduserspokemonsid = t2.id

Answer 2

The specific problem comes from the use of implicit joins first and then an explicit join. Lesser lines of code is not a very good reason to use implicit joins, specially since it's deprecated.

Another considerations would be to use table aliases, and also to prefix every column with the corresponding table alias, even if the column is unique between those tables for readability and a code that's easier to maintain.

You are also missing the GROUP BY needed for your aggregation function. All in all, the fixed code would be:

SELECT TOP 10 username, 
              NAME, 
              T1.authoritylevel, 
              totalcount, 
              avglevel, 
              T2.id, 
              T2.pokemonid, 
              pokemonlevel, 
              pokemonexp, 
              battlecount, 
              battlevictorycount, 
              T1.userid, 
              Count(T5.offereruserid) AS OfferCount 
FROM   Table_1 T1
INNER JOIN Table_2 T2
    ON T1.userid = T2.userid
INNER JOIN Table_3 T3
    ON T2.pokemonid = T3.pokemonid
INNER JOIN Table_4 T4
    ON T2.pokemonid = T4.pokemonid
INNER JOIN Table_5 T5
       ON T5.offereduserspokemonsid = T2.id 
WHERE  pokemonplace = 'trade' 
       AND T2.userid != 1 
GROUP BY  username, 
          NAME, 
          T1.authoritylevel, 
          totalcount, 
          avglevel, 
          T2.id, 
          T2.pokemonid, 
          pokemonlevel, 
          pokemonexp, 
          battlecount, 
          battlevictorycount, 
          T1.userid;

But, as I said, I suggest that you add the corresponding prefixes to those columns.