CODEWITHSUNDEEP
mysqlsql
Sai Datta
Sai Datta

Reputation: 935

How to use WHERE inside sub SELECT?

I am trying to sub-select from a table of users .

But I get an error saying You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS n1 ) LIMIT 0, 25' at line 7

I do not understand what I'm doing wrong here

SELECT * FROM ( SELECT 
               n1.userid,
               n1.country,
               n1.gender
               FROM users 
               WHERE n1.country = 'US' 
               AND n1.gender = '1' AS n1 )

Upvotes: 0

Views: 84

Answers (3)

nozzleman
nozzleman

Reputation: 9649

You did put the alias at the false place. Try

SELECT * FROM (SELECT 
               n1.userid,
               n1.country,
               n1.gender
               FROM users AS n1  -- <AS n1> this should be here
               WHERE n1.country = 'US' 
               AND n1.gender = '1')

or

SELECT * FROM (SELECT 
               n1.userid,
               n1.country,
               n1.gender
               FROM users 
               WHERE n1.country = 'US' 
               AND n1.gender = '1') AS n1  -- or here

However, if the query isn't just a simplified form, the whole query can also be written without the subquery:

SELECT userid, country, gender
FROM users 
WHERE country = 'US' 
AND gender = '1'

Upvotes: 1

Rahul
Rahul

Reputation: 77866

It should be like below since that AS n1 refers to inline view alias. Again, you are trying to use the same alias to refer your column in subquery that's another mistake.

SELECT * FROM ( SELECT 
               userid,
               country,
               gender
               FROM users 
               WHERE country = 'US' 
               AND gender = '1') AS n1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

I think this is what you want:

SELECT *
FROM (SELECT n1.userid, n1.country,  n1.gender
      FROM users n1
      WHERE n1.country = 'US' AND n1.gender = '1' 
     ) n1;

as doesn't belong in the WHERE clause. You need n1 as a table alias in the subquery, if you are going to use it to qualify column names. And, you should give the outer query a table alias.

Note: Obviously the subquery is not needed, but I am guessing you really do want it for your actual problem.

Upvotes: 6

Related Questions