SQL Server Nested Select (with JOIN) issue

Question

I am attempting to understand how nesting works in SQL server, and have produced the following code...

SELECT(*)
FROM
(
    SELECT (*)
    FROM MPOG_Institutions JOIN AIMS_Patients 
    ON MPOG_Institutions.MPOG_Institution_ID = AIMS_Patients.MPOG_Institution_ID
) AS a

My current understanding is that the inner SELECT, FROM, and JOIN statement produces a result set that is then used in the FROM statement of the outer SELECT statement. However, when this code is run I get the following syntax error:

Msg 8156, Level 16, State 1, Line 1
The column 'MPOG_Institution_ID' was specified multiple times for 'a'.

I have read that these nested result sets need aliases, hence the "AS a", but then this error occurs. Can anyone help me understand what is going on here?

Thank you!

LittleBobbyTables - Au Revoir · Accepted Answer · 2013-05-23 14:05:27Z

5

That's because MPOG_Institution_ID is in both tables, and by doing SELECT * in the outermost query, you have selected the column twice. Since you haven't an alias to any of the columns in the subquery, SQL doesn't know what to do with two columns named the same, hence the error.

Instead of doing SELECT *, you should specifically call out each column, and in the case of MPOG_Institution_ID, select it only once.

edited May 23, 2013 at 14:05

answered May 23, 2013 at 14:00

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server Nested Select (with JOIN) issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related