SQL - The column was specified multiple times-CodePudding

I am getting the following error when trying to run this query in SQL:

SELECT *
                            FROM [Submissions] AS [s]
                            INNER JOIN [SubmissionHistories] AS [s0] ON [s].[Id] = [s0].[SubmissionId]
                            INNER JOIN
                            ( SELECT TOP 1 WITH TIES *
                                FROM OHis
                                ORDER BY row_number() OVER (PARTITION BY ApplicationId ORDER BY OwnerChangeDate DESC)
                            ) AS [oh] ON [oh].[ApplicationId] = [s].ApplicationId
                            INNER JOIN 
                            ( SELECT TOP 1 WITH TIES *
                                FROM AHis
                                ORDER BY row_number() OVER (PARTITION BY ApplicationId ORDER BY AuthorizedSubmitterChangeDate DESC)
                            ) AS [ash] ON [ash].[ApplicationId] = [s].ApplicationId

The column 'ApplicationId' was specified multiple times for 'Submissions'. What am I missing?

CodePudding user response：

What is happening here is that a column called ApplicationId appears in more than one table. Because you are doing a blanket SELECT *, your SQL engine can't deal with this. The corrective action would be to prefix ApplicationId with an alias scoping it to a particular table. For example, assuming that Submissions and SubmissionHistories have an ApplicationId column, you could use:

SELECT s.ApplicationId AS sAId, s0.ApplicationId AS s0AId, -- other columns here
FROM ...

Note that, because of problems like the one you are seeing, it is always preferable to explicitly list out all columns you select.

CodePudding user response：

Prefix the column name with the table alias created in your join statement.

A.Wine_ID,
A.Grape_Name,
A.Grape,
B.Grape, 
B.Grape_Vineyard_Location
FROM WINE_TABLE A
JOIN GRAPE_VINEYARD_TABLE B
ON A.Wine_ID=B.Wine_ID

The prefix (A, B) before the 'Grape' column in my select statement allowed me to pull in the Grape column from both the WINE_TABLE A and GRAPE_VINEYARD_TABLE B.