MySQL Subquery in SELECT statement

Question

I am using MySQL and am getting an error when if I try to excute a subquery... my subquery is as follows:

sponsor_id columns contains 10 record with id (auto increment).

SELECT * FROM user where id=(select id from user where sponsor_id ='10002')

Thanks in advance

Answer 1

The reason is that your subquery is most probably returning more than one values. It should return only one value if you are using the equals to operator.

Else use the IN clause as:

SELECT * FROM user where id IN (select id from user where sponsor_id ='10002')

Edit:

You may also use an INNER JOIN or any other JOIN for that matter that suits your purpose.

Answer 2

Try this

SELECT * FROM user where id IN (select id from user where sponsor_id ='10002')

or:

SELECT * FROM user where id =(select id from user where sponsor_id ='10002' Limit 1)

Answer 3

you are getting the error because '=' will operate on a single value not on the multiple values.so use 'IN' opeartor or make sure that your subquery returns only a single value while using '='.

Answer 4

use IN instead of = .

SELECT * FROM user where id IN (select id from user where sponsor_id ='10002' AND id IS NOT NULL)