CODEWITHSUNDEEP
mysql
learn004
learn004

Reputation: 3

using AND operator with two select?

i was doing some tests about sql in my lab , so i try to make 2 select statements with AND operator :

mysql> select * from users where id = 1 and select * from users where id = 2;

and give me this error :

ERROR 1064 (42000): 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 'select * from users where id = 2' at line 1

i search in web and solve this by adding () in second select :

mysql> select * from users where id = 1 and (select * from users where id = 2);

and this error showing up :

ERROR 1241 (21000): Operand should contain 1 column(s)

i solve this by make the select in subquery :

mysql> select * from users where id = 1 and (select 1 from (select * from users where id = 2)a);

and i got this result :

+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | Dumb     | Dumb     |
+----+----------+----------+
1 row in set (0.00 sec)

so why the result not are the id = 1 and id = 2 ? why just id 1 result appear in table ?

i expect this result:

+----+----------+------------+
| id | username | password   |
+----+----------+------------+
|  1 | Dumb     | Dumb       |
|  2 | Angelina | I-kill-you |
+----+----------+------------+
2 rows in set (0.02 sec)

*i dont want to solve this , i need to know why using and with two select not give me the two results?

Upvotes: 0

Views: 45

Answers (1)

Jim Wright
Jim Wright

Reputation: 6058

You can get those results with a much simpler statement:

select * from users where id = 1 or id = 2;

If you really want that format, you should be using UNION.

select * from users where id = 1 UNION select * from users where id = 2;

select * from users where id = 1 and (select 1 from (select * from users where id = 2)a); so why the result not are the id = 1 and id = 2 ? why just id 1 result appear in table ?

The query is getting the record where id = 1 and then using your other statement as an extra condition.

Upvotes: 1

Related Questions