Error in a sql query :The column prefix 'XX does not match with a table name or alias name used in the query

  • I can't get this query to run i keep gettiing error :-

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

    Any ideas what 'm doing wrong???

    SELECT

    L.LOC_ID,

    L.Location,

    L.Phone,

    L.DealerCode,

    (FirstName + ' ' + LastName) AS Manager,

    l.territory_code as storecode,

    wcw.store_code

    FROM

    (select top 1 [store_code] FROM [sampledb].[dbo].[System_User] where [territory_code] = L.territory_code) wcw,

    Location L ,

    mwc.dbo.Employee E

    WHERE

    L.Manager_ID *= E.EM_ID

    and L.DateClosed IS NULL

    and l.loc_id = '109'

    and wcw.store_code ='374'

  • Hi Patrick,

    In the above I refers to which table?

    l.territory_code, l.loc_id

    Ganapathi M

  • thanks for the reply.

    it refers to the Location table

    thanks in advance

  • Hi Patrick,

    Please do some changes. Because no alias for 'I'

    ~~~~~~~~~~~~~~~~~~~~

    l.territory_code as storecode,

    and l.loc_id = '109'

    ~~~~~~~~~~~~~~~~~~~~

    The above should be

    L.territory_code as storecode,

    and L.loc_id = '109'

    Regards,

    Ganapathi M

  • Thanks but that wasn't the problem though

  • I have a feeling that the error's coming from the sub query. Try changing to the following and see if your problem goes away.

    SELECT

    L.LOC_ID,

    L.Location,

    L.Phone,

    L.DealerCode,

    (FirstName + ' ' + LastName) AS Manager,

    l.territory_code as storecode,

    wcw.store_code

    FROM

    (select [store_code], [territory_code] FROM [sampledb].[dbo].[System_User]) wcw INNER JOIN

    Location L ON WCW.[territory_code] = L.territory_code ,

    mwc.dbo.Employee E

    WHERE

    L.Manager_ID *= E.EM_ID

    and L.DateClosed IS NULL

    p.s. you should consider changing the *= to use the outer join syntax, as the older join style is deprecated in SQL 2000

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Although i fixed it.....but i will try your advice and let u know.

    Thanks

  • What was the problem?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

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