SQL 'Invalid column name ' error, with inner select

Question

i have the following SQL:

SELECT A.*, 
(SELECT answer FROM [tblAnswers] B 
WHERE  B.memberID = A.memberID AND QuestionID = 3) AS ethnicity
FROM [tblMembers] A  
WHERE 
LOWER(gender) = 'm' 
AND ethnicity = 'Sephardi'

and i get the error Invalid column name 'ethnicity' referring to the last reference to that column.

Question: How come this column is not available to the WHERE clause?

I know that if i do the following, it works:

SELECT A.*, 
(SELECT answer FROM [tblAnswers] B WHERE  B.memberID = A.memberID AND QuestionID = 3) AS ethnicity
FROM [tblMembers] A  
WHERE
LOWER(gender) = 'm' 
AND convert(nvarchar, (SELECT answer FROM [tblAnswers] B WHERE  B.memberID = A.memberID AND QuestionID = 3)) = 'Sephardi'

You can't, it's a limitation of sql server: http://stackoverflow.com/questions/16161938/how-to-use-alias-column-name-in-where-clause-in-sqlserver — Jan Van Herck, Nov 19 '13 at 10:46
I see little reason _not_ to use a simple join, actually. It will also solve your problem. — oerkelens, Nov 19 '13 at 11:10

Obsidian Phoenix · Answer 1 · 2013-11-19T11:03:03.517

3

You cannot use "Computed" columns in your where clause without specifically referencing the entire computation (query, case, etc).

You should join the Query table instead of subquerying it:

Select A.*, B.answer
From tblMembers A
    Inner Join tblAnswers B on A.memberID = B.membeID and A.QuestionID = 3
Where Lower(gender) = 'm'
    and B.answer = 'Sephardi'

edited Nov 19 '13 at 11:03

answered Nov 19 '13 at 10:48

Obsidian Phoenix

4,083
1
22
60

Why `LEFT JOIN` instead of `INNER JOIN`? – Tim Schmelter Nov 19 '13 at 10:56
Habit really, no other reason. It could equally be both in this case. I've updated the select to be inner given the specific scenario. – Obsidian Phoenix Nov 19 '13 at 11:00

score 2 · Accepted Answer · answered Nov 19 '13 at 10:47

2

You can't use a column name you've just assigned in the SELECT clause in the following WHERE clause. As a general case you can put it in a subquery and then use the column name:

SELECT * FROM
(
    SELECT A.*, 
    (SELECT answer FROM [tblAnswers] B 
    WHERE  B.memberID = A.memberID AND QuestionID = 3) AS ethnicity
    FROM [tblMembers] A  
    WHERE LOWER(gender) = 'm' 
) s
WHERE ethnicity = 'Sephardi'

This query might be better suited to a JOIN though:

SELECT A.*, t.answer AS ethnicity
FROM [tblMembers] A  
LEFT JOIN tblAnswers t ON b.memberID = A.memberID AND QuestionID = 3
WHERE LOWER(gender) = 'm' AND t.answer = 'Sephardi'

answered Nov 19 '13 at 10:47

OGHaza

4,795
7
23
29

thanks. i would need `LEFT JOIN` as these secondary rows may not exist. i have 3 of these subquery fields to filter with. which method is better resource-wise? – kneidels Nov 19 '13 at 12:27
Personally I use a `JOIN` 100% of the time - I'm sure there are cases where a subquery might be better but I'm yet to come across one in practice. – OGHaza Nov 19 '13 at 12:29

SQL 'Invalid column name ' error, with inner select

2 Answers2