Sql Server 2008 Specified Column Was Specified Multiple Times

The below code throws the following exception. How can I fix this? Msg 8156, Level 16, State 1, Line 17 The column 'id' was specified multiple times for 'QISproduct'. Query: SE

Solution 1:

The computed query aliased to QISProduct contains id column from two tables i.e. QISEventLog and QISProcess. So rename those columns to different names. Updated query

SELECT g.artcode
            ,sum(g.aantal)
            ,i.class_01
            ,i.Isstockitem
        FROM gbkmut AS g
        INNER JOIN items AS i ON i.itemcode = g.artcode
        INNER JOIN (
            SELECT QISP.id
                ,QISprocess.nml AS Processnml
                ,QISeventlog.id AS EventLogId
                ,QISeventlog.dtsample
                ,QISproduct.nms
                ,QISbatchlog.nm AS batchnm
                ,QIStestlog.idvariable
                ,QIStestlog.no
                ,QISshortnote.ds
                ,gewicht = CASE QIStestlog.IDvariable
                    WHEN'139'THEN QIStestlog.no
                    END
                ,aantal = CASE QIStestlog.IDvariable
                    WHEN'234'THEN QIStestlog.no
                    END
                ,siloleeg = CASE QIStestlog.idvariable
                    WHEN'23'THENCASE QIStestlog.no
                                WHEN'10'THEN'Ja'ELSE'Nee'ENDEND
                ,QISvariable.nml variablenml
                ,gl.nm AS glnm
                ,QISprocess.id AS ProcessId
            FROM QIC.Vobra_new2.dbo.production AS QISP
            INNER JOIN QIC.Vobra_new2.dbo.process AS QISprocess ON QISP.idprocess = QISProcess.id
            INNER JOIN QIC.Vobra_new2.dbo.product AS QISproduct ON QISP.idproduct = QISproduct.id
            INNER JOIN QIC.Vobra_new2.dbo.batchlog AS QISbatchlog ON QISP.idbatch = QISbatchlog.id
            INNER JOIN QIC.Vobra_new2.dbo.eventlog AS QISeventlog ON QISeventlog.idproduction = QISP.id
                AND QISeventlog.idbatch = QISbatchlog.id
            INNER JOIN QIC.Vobra_new2.dbo.testlog AS QIStestlog ON QIStestlog.idevent = QISeventlog.id
            LEFT JOIN QIC.Vobra_new2.dbo.shortnote AS QISshortnote ON QISshortnote.id = QIStestlog.no
                AND QIStestlog.idvariable = '144'
            INNER JOIN QIC.Vobra_new2.dbo.variable AS QISvariable ON QISvariable.id = QIStestlog.idvariable
            LEFT JOIN QIC.Vobra_new2.dbo.vvarxproc AS vvp ON vvp.idvariable = QISvariable.id
                AND vvp.idprocess = QISP.idprocess
            LEFT JOIN QIC.Vobra_new2.dbo.attribute AS QISattribute ON QISattribute.id = vvp.idattribute
            LEFT JOIN QIC.Vobra_new2.dbo.grade AS QISgrade ON QISgrade.id = QISattribute.idgrade
            LEFT JOIN QIC.Vobra_new2.dbo.gradelevel AS gl ON gl.idgrade = QISattribute.idgrade
                AND gl.nlevel = QIStestlog.no
            WHERE QISbatchlog.nm NOTLIKE'V%'AND QISP.dtstart > '2017-01-01'AND QISP.dtstart < '2017-01-19'AND QISP.idprocess IN (
                    '12'
                    ,'13'
                    ,'14'
                    ,'15'
                    ,'16'
                    ,'17'
                    ,'18'
                    ,'41'
                    )
                AND QIStestlog.idvariable IN (
                    '234'
                    ,'139'
                    ,'128'
                    )
            ) QISproduct ON g.artcode = QISproduct.nms
        WHERE g.bkjrcode > '2015'AND g.reknr IN (
                3000
                ,3010
                ,3020
                )
            AND g.aantal > 0AND g.warehouse IN (
                '1'
                ,'9'
                )
            AND g.datum >= '2017-01-01'AND g.oorsprong = 'R'AND g.kstplcode <> 'VPR'GROUPBY g.artcode
            ,i.Class_01
            ,i.IsStockItem

Solution 2:

, QISbatchlog.nm And , gl.nm Have same column name You can add as to change colum name , gl.nm as col1

Solution 3:

You have multiple issues in your query. You are populating following columns with same column name in your inner query. Use Unique name using AS alias in inner query.

QISP.id
QISeventlog.id
QISprocess.id

gl.nm
QISbatchlog.nm

QISprocess.nml
QISvariable.nml

It is mandatory to have unique column name return by select list in sql.