question

Phil 1 avatar image
Phil 1 asked

column was specified multiple times when using a join

Hi Im getting the error

Msg 8156, Level 16, State 1, Line 15 The column 'ACTIONTYPE' was specified multiple times for 'paged'.

when running this query

WITH paged AS

(

SELECT *, ROW_NUMBER() OVER (ORDER BY DESCRIPTION) AS 'rowNumber'

FROM [IQ_ITEM]

FULL JOIN IQ_PROMOTIONSITEMS ON IQ_ITEM.NO = IQ_PROMOTIONSITEMS.ITEMNO

WHERE IQ_POMOTIONSITEMS.ITEMNO = IQ_ITEM.NO

AND DONOTSHOW = '0'

AND CODE='%'

AND NO+DESCRIPTION LIKE'%'

)

SELECT * FROM paged WHERE rowNumber BETWEEN 1 and 10;

is there anyway to exclude columns from the joined table so i dont get this error? or any ideas on how to alter this query

Thanks in advance

sql-server-2008joinspaging
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Piotr Rodak avatar image
Piotr Rodak answered

You have ACTIONTYPE column in both tables that you join in the CTE. Modify the query to list all columns that you need explicitly, selecting * is not the best idea.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jason Cumberland avatar image
Jason Cumberland answered

You need to specify the column from each table that you want in the CTE definition and not do a "select *", see below. I have aliased the table named to make it a little clearer which columns com from which table. If the column exists in both tables you need to put a column list in place so the query processor knows which one you want to display.

WITH paged AS            
            
(            
            
SELECT IQI.col1, IQI.col3, IQP.col1, IQP.col2, ROW_NUMBER() OVER (ORDER BY DESCRIPTION) AS 'rowNumber'            
            
FROM [IQ_ITEM] IQI            
            
FULL JOIN IQ_PROMOTIONSITEMS IQP ON IQ_ITEM.NO = IQ_PROMOTIONSITEMS.ITEMNO            
            
WHERE IQ_POMOTIONSITEMS.ITEMNO = IQ_ITEM.NO            
            
AND DONOTSHOW = '0'            
            
AND CODE='%'            
            
AND NO+DESCRIPTION LIKE'%'            
            
)            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.