Post reply

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

  • May 17, 2005 at 10:02 am

    #66426

    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

  • May 17, 2005 at 10:45 am

    #559318

    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