CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
user8001246
user8001246

Reputation:

The column was specified multiple times

I get an error with the following sql query. It says

"The column 'Type' was specified multiple times for 'M'".

The 'M' after

(
    SELECT *
    FROM [ToolManagement].[dbo].[tblTools] K
    JOIN [ToolManagement].[dbo].[tblToolTypes] L ON K.ToolType = L.ToolType
)

is underlined in SQL Server Management Studio.

    SELECT          A.EntryID, A.Time, B.Name, B.Number, C.Number, D.StationNr, E.Number, E.Pressure, F.Nickname, F.Number, G.Format, G.Format, G.ID2, H.Description, I.Comment, M.Description
    FROM            [...]   A
    JOIN            [...]   B ON A.EntryID = B.EntryID
    JOIN            [...]   C ON A.EntryID = C.EntryID
    JOIN            [...]   D ON A.EntryID = D.EntryID
    JOIN            [...]   E ON A.EntryID = E.EntryID
    JOIN            [...]   F ON A.EntryID = F.EntryID
    JOIN            [...]   G ON A.EntryID = G.EntryID
    JOIN            [...]   H ON A.EntryType = H.EntryType
    LEFT OUTER JOIN [...]   I ON A.EntryID = I.EntryID
    JOIN            (SELECT * FROM [...] K JOIN [...] L ON K.Type = L.Type) M ON A.ID = M.ID
    WHERE           A.ID = '1234'

Upvotes: 1

Views: 15378

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

The problem here is that in the subquery you have aliased as M you are doing SELECT *, and the two tables involved in the join both have a Type column. SQL Server is rightfully complaining that it doesn't know which Type column you want to select.

You only use a single type column from your subquery, and the join condition ensures that both are the same, so just select one of them:

SELECT
    A.EntryID, A.Time, B.Name, B.Number, C.Number, D.StationNr, E.Number,
    E.Pressure, F.Nickname, F.Number, G.Format, G.Format, G.ID2, M.Description,
    I.Comment, M.Type
FROM            [...]   A
JOIN            [...]   B ON A.EntryID = B.EntryID
JOIN            [...]   C ON A.EntryID = C.EntryID
JOIN            [...]   D ON A.EntryID = D.EntryID
JOIN            [...]   E ON A.EntryID = E.EntryID
JOIN            [...]   F ON A.EntryID = F.EntryID
JOIN            [...]   G ON A.EntryID = G.EntryID
JOIN            [...]   H ON A.EntryType = H.EntryType
LEFT OUTER JOIN [...]   I ON A.EntryID = I.EntryID
JOIN            (SELECT L.Type, L.Description FROM [...] K JOIN [...] L
                     ON K.Type = L.Type) M ON A.ID = M.ID
WHERE           A.ID = '1234'

Note that a select statement at the top level certainly can have two or more columns/aliases with the same name. The problem only arises when trying to select from such a table.

Upvotes: 2

SchmitzIT
SchmitzIT

Reputation: 9572

The issue is that your inner query is selecting a type column from both K and L. Seeing your inner query selects every column from every table, it will thus have 2 type columns in the result; 1 from K, and one from L. You will need to ensure that only 1 column named type is returned, or that the other column is aliased to have a different name.

SELECT * FROM [...] K JOIN [...] L ON K.Type = L.Type

K.Type and M.Type both will be returned as M.type, and thus your outer query is not sure which of the two to return. If Type is the only column you actually need from that sub-query, just return K.type:

SELECT K.Type FROM [...] K JOIN [...] L ON K.Type = L.Type

Upvotes: 2

Related Questions