CODEWITHSUNDEEP
mysql
rg88
rg88

Reputation: 20977

MySQL - Using an alias in a subquery with WHERE clause

I have a feeling I am completely borking this MySQL query but I'll ask anyway. I am wondering why I get the warning Unknown column 'FOO', and how I can get this query to work properly, in 'where clause' when I run the following:

SELECT sample_id AS FOO 
FROM   tbl_test 
WHERE  sample_id = 521 
       AND sample_id IN (SELECT sample_id 
                         FROM   tbl_test 
                         WHERE  sample_id = FOO 
                         GROUP  BY sample_id)

Edit This query works fine on a different server and fails as described above on the new server. The old one was v 5.0.45 and the new one is 5.0.75.

Upvotes: 1

Views: 2192

Answers (1)

Unreason
Unreason

Reputation: 12704

SELECT sample_id 
FROM   tbl_test outter 
WHERE  sample_id = 521 
       AND sample_id IN (SELECT sample_id 
                         FROM   tbl_test 
                         WHERE  sample_id = outter.sample_id 
                         GROUP  BY sample_id)

Upvotes: 3

Related Questions