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

Question

Post reply

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

Patrick.I

Hall of Fame

Points: 3105
More actions
December 19, 2007 at 11:21 pm

#62325

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'

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

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

Ganapathi M Say Hey Kid Points: 707 More actions · Answer 1

Hi Patrick,

In the above I refers to which table?

l.territory_code, l.loc_id

Ganapathi M

Patrick.I Hall of Fame Points: 3105 More actions · Answer 2

thanks for the reply.

it refers to the Location table

thanks in advance

Ganapathi M Say Hey Kid Points: 707 More actions · Answer 3

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

Patrick.I Hall of Fame Points: 3105 More actions · Answer 4

Patrick.I

Hall of Fame

Points: 3105

December 20, 2007 at 9:40 pm

#761971

Thanks but that wasn't the problem though

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

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

Patrick.I Hall of Fame Points: 3105 More actions · Answer 6

Although i fixed it.....but i will try your advice and let u know.

Thanks

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

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