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

well, you **are** aware of explicit joins, so why mix it with implicit ones?. Besides, this will fail because there's no `GROUP BY` — Lamak, Aug 17 '16 at 19:21
@Lamak well because they are same performance and easier to read for me :D it requires lesser lines of code : http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins . ah let me check group by — Furkan Gözükara, Aug 17 '16 at 19:23

score 2 · Accepted Answer · answered Aug 17 '16 at 20:34

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

score 1 · Answer 2 · answered Aug 17 '16 at 19:32

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.

ty for the answer let me try. also i have rewritten the question to prevent group by can you check my updated question? — Furkan Gözükara, Aug 17 '16 at 19:34
actually i have got a lot of dynamic filtering which adds a lot of "and" clauses to the end . so turning them into explicit joins seriously breaks my code :( — Furkan Gözükara, Aug 17 '16 at 19:36
@MonsterMMORPG well, I can only answer to the scope of this question. If that's the case, then use only implicit joins, not a mix of implicit and explicit ones — Lamak, Aug 17 '16 at 19:38
so no way to fix without turning everything into explicit join. by the way what about the updated question? i mean adding that inline select somehow even when used explicit join — Furkan Gözükara, Aug 17 '16 at 19:42
@MonsterMMORPG as I said, you could use only implicit joins, but I'm not gonna write that answer (because I don't like them). About the updated question, yeah, I'll need to change my whole answer **and** also understand what that last column is supposed to be, and now I just don't have the time — Lamak, Aug 17 '16 at 19:43

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

2 Answers2