Im trying to find the error in this statement:

CREATE PROCEDURE STP_selectmain


AS

select a.inventoryid, b.firstname, b.lastname, art.title, art.medium,
a.cost, a.inventoryid, a.receivedate, a.dimensions,
a.reference, art.provenance, sum(c.restorationcost),
sum(d.framingcost), sum(e.cost)
from art as a left outer join artist as b on a.artistid =
b.artistid,
a left outer join restoration as c on a.inventoryid =
c.inventoryid,
a left outer join outframing as d on a.inventoryid =
d.inventoryid,
a left outer join basiccosts as e on a.inventoryid =
e.inventoryid

group by a.inventoryid, b.firstname, b.lastname, a.title, a.medium,
a.cost, a.inventoryid, a.receivedate, a.dimensions, a.reference,
a.provenance
order by a.inventoryid desc
GO


eveytime I do a syntax check on it I get this error.

error 1012: the correlation name 'a' has the same exposed name as
table 'a'.

Whats the syntax to fix this?

thanks

-Jim

Jim (jim.ferris@motorola.com) writes:
> select a.inventoryid, b.firstname, b.lastname, art.title, art.medium,
> a.cost, a.inventoryid, a.receivedate, a.dimensions,
> a.reference, art.provenance, sum(c.restorationcost),
> sum(d.framingcost), sum(e.cost)
> from art as a left outer join artist as b on a.artistid =
> b.artistid,
> a left outer join restoration as c on a.inventoryid =
> c.inventoryid,
> a left outer join outframing as d on a.inventoryid =
> d.inventoryid,
> a left outer join basiccosts as e on a.inventoryid =
> e.inventoryid
>
> group by a.inventoryid, b.firstname, b.lastname, a.title, a.medium,
> a.cost, a.inventoryid, a.receivedate, a.dimensions, a.reference,
> a.provenance
> order by a.inventoryid desc
> GO
>...
> error 1012: the correlation name 'a' has the same exposed name as
> table 'a'.
>
> Whats the syntax to fix this?

You have a mix of old and new FROM syntax, and I would suppose that
you mean:

FROM art AS a
LEFT JOIN artist AS b ON a.artistid = b.artistid
LEFT JOIN restoration AS c ON a.inventoryid = c.inventoryid
LEFT JOIN outframing AS d ON a.inventoryid = d.inventoryid
LEFT JOIN asiccosts AS e on a.inventoryid = e.inventoryid


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp