Weird Error (or don''t mix multi-table queries and joins)

  • So I'm updating some legacy code and I get the following error:

    The column prefix 'p' does not match with a table name or alias name used in the query.

    Hmm... that's weird. I look at the SQL in the query and it looks like this:

      ...
      select
    p.*
    , t.Name as PersonTypeName
      from[Person] p (nolock),
        PersonType t (nolock)
      inner join #GroupList gl on gl.PersonID = p.PersonID -- this line just added
      where   p.PersonTypeID= t.PersonTypeID and
    p.PersonTypeID= @PersonTypeID and
    p.DelFlag = 'N' and
    p.ActiveFlag = 'Y'
    
      -- #GroupList is created above this portion.  That part works fine.
    

    Poking at this for a while, I discover that you can't do a join and a multi-table query. I guess that makes sense (and I've never run into this before since I always use joins)... So, I change the query to inner join PersonType as well and everything is good.

    Just thought this might save someone else 20 minutes of headscratching...



    -Brandon

  • You are right. T-SQL does not like when you mix old style joins (the join definition in the where clause) and new style (join table B ON...). If you stick to one style in this query, you are fine. The multi table joins are not the problem.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply