MySQL: Search a value in a first table. If not exist, search it in second table

Question

I want to identify account inputted if it is an admin account or an user account and search the username and password entered in admin table if it is not there search it into "users table"and this is the error i got operand should contain 1 column(s)

this is the query i tried

   Query = "select admin.user,admin.pass from admin where user= '" & username.Text & "' and pass= '" & password.Text & "'" not in users.user,users.pass"

**WARNING**: It's time to learn about [SQL injection bugs](http://bobby-tables.com) because this code is full of them. — tadman, Nov 07 '17 at 06:00

score 0 · Answer 1 · answered Nov 07 '17 at 04:36

The error is pretty obvious: only single column allowed to pass for IN or NOT IN clause to determine relevant values assigned for that column.

You can fix the query by providing single column from a subquery like this example:

Dim Query As String = "SELECT admin.user, admin.pass FROM admin WHERE user = '" & username.Text & _
                      "' AND pass = '" & password.Text & _ 
                      "' OR user [NOT] IN (SELECT users.user FROM users WHERE user = '" & username.Text & _ 
                      "' AND pass = '" & password.Text & "')"

NB: Rather than concatenating value strings, use parameterized query version instead:

Dim Query As String = "SELECT admin.user, admin.pass FROM admin WHERE user = @username AND pass = @password OR user [NOT] IN (SELECT users.user FROM users WHERE user = @username AND pass = @password)"

Similar issue:

Operand Should Contain 1 Column - MySQL NOT IN

MySQL: Search a value in a first table. If not exist, search it in second table

1 Answers1