SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Error with join - "column was specified multiple"
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shawnmolloy
Yak Posting Veteran

USA
93 Posts

Posted - 06/03/2007 :  02:17:54  Show Profile  Visit shawnmolloy's Homepage  Reply with Quote
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)

Singapore
17645 Posts

Posted - 06/03/2007 :  02:23:11  Show Profile  Reply with Quote
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

USA
93 Posts

Posted - 06/03/2007 :  04:28:47  Show Profile  Visit shawnmolloy's Homepage  Reply with Quote
Yes that worked! Thanks :)
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 06/04/2007 :  01:35:05  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000