Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Error with join - "column was specified multiple"

Author  Topic 

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-06-03 : 02:17:54
Hello,

I get the following error when I try to create this stored proc. I think it may have something to do with the two joins.

Msg 8156, Level 16, State 1, Procedure sp_wisetopic_landing_getPaged_FeaturedAlbums, Line 16
The column 'ArtistId' was specified multiple times for 'PagedData'.

Here is the QUERY:


create procedure sp_wisetopic_landing_getPaged_FeaturedAlbums

@pageSize int,
@pageNumber int

AS

DECLARE @FirstRow INT,
@LastRow INT

SELECT @FirstRow = ( @PageNumber - 1) * @PageSize + 1,
@LastRow = @PageSize + (@PageNumber - 1) * @PageSize;

WITH PagedData AS
(
SELECT A.*,
C.ArtistId,

ROW_NUMBER() OVER (ORDER BY sortOrder DESC) AS RowNumber
FROM
wisetopic_artist_album A
INNER JOIN
wisetopic_artist_featuredAlbums B
ON
A.albumId = B.albumId
INNER JOIN
wisetopic_artist c
ON
A.artistid = c.artistid
)
SELECT RowNumber, *
FROM PagedData
WHERE RowNumber between @FirstRow AND @LastRow
ORDER BY RowNumber ASC;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-03 : 02:23:11
You have 2 column of same name ArtistId in the result set.
do you still need the C.ArtistId in the result set as C.ArtistId is actually same as A.ArtistId
Either removed C.ArtistId from the result set or use column alias.

SELECT A.*,
C.ArtistId as C_ArtistId,



KH

Go to Top of Page

shawnmolloy
Yak Posting Veteran

93 Posts

Posted - 2007-06-03 : 04:28:47
Yes that worked! Thanks :)
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-04 : 01:35:05
also be careful of using select * in production code - it's a no-no.

if someone adds a column to the table/view, callers of this proc may break.


www.elsasoft.org
Go to Top of Page
   

- Advertisement -