MonsterMMORPG MonsterMMORPG - 3 months ago 7
SQL Question

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

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

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