Home / Questions / 14272
Accepted
Máxima Alekz
Asked: 2020-06-18 03:55:03 +0800 CST 2020-06-18 03:55:03 +0800 CST

MySQL - Using AND & OR in the same query

  • 772

I have a table like this, which has two rows:

nombre_per   | apellido_per   | permisos_busqueda_per
-------------+----------------+----------------------
Mattew       | Janeey         | SI
Janny        | Pineda         | NO

My question is the following

SELECT nombre_per, apellido_per, permisos_busqueda_per 
FROM   la_tabla 
WHERE  nombre_per LIKE '%a%' OR apellido_per LIKE '%a%' AND permisos_busqueda_per="SI"

If I'm looking only for users who have the findable permission enabled permisos_busqueda_per, why does it return both rows if Janny doesn't have the findable permission enabled?

mysql

6 Answers

  1. Best Answer

    This is a problem with the precedence of the operators, as you can see in the MySQL documentation , the AND operator takes precedence over the OR, this makes your SELECT select based on these conditions:

    • people who have a name with the letter "a"; EITHER
    • people who have a last name with the letter "a" AND permissions equal to "SI"

    when really what you want is this:

    • people who have a first or last name with the letter "a"; Y
    • permissions equal to "YES"

    The solution is simple: use parentheses:

    SELECT nombre_per, apellido_per, permisos_busqueda_per 
FROM   la_tabla 
WHERE  (nombre_per LIKE '%a%' OR apellido_per LIKE '%a%') AND permisos_busqueda_per="SI"
    • 20

  2. Try grouping the conditions with parentheses

    SELECT nombre_per, apellido_per, permisos_busqueda_per FROM la_tabla WHERE (nombre_per LIKE '%a%' OR apellido_per LIKE '%a%') AND permisos_busqueda_per="SI
    • 4

  3. You have an error in the query, you have to change:

    SELECT * FROM nombre_per, apellido_per, permisos_busqueda_per FROM la_tabla WHERE nombre_per LIKE '%a%' OR apellido_per LIKE '%a%' AND permisos_busqueda_per="SI"

    For this:

        SELECT nombre_per, apellido_per, permisos_busqueda_per FROM la_tabla WHERE nombre_per LIKE '%a%' AND permisos_busqueda_per= 'SI'
 OR apellido_per LIKE '%a%' AND permisos_busqueda_per= 'SI'

    The problem was in the 'or'. Hope this can help you

    • 2

  4. As they have commented in the other answers, your problem is that the operators according to the documentation do not follow the process that you expect; I recommend that when you use operations you do it like this:

    SELECT nombre_per, apellido_per, permisos_busqueda_per 
FROM   la_tabla 
WHERE  (nombre_per LIKE '%a%' OR apellido_per LIKE '%a%') 
        AND permisos_busqueda_per="SI"

    Separating the ANDs in different lines and always using them ()in each line.

    • 2

  5. Same thing, plus I'm not sure about mysql, but in sql you have to use single quotes around strings, i.e. ' instead of "

    • 0

  6. other people have already documented it, but what you have to know is that the order of the factors greatly influences the queries, it brings you the two values ​​because the last thing it is doing is the OR, that is why you must put it inside the parentheses to differentiate between one operation and another so that the manager cannot be confused

    SELECT per_name, per_last, per_search_permissions FROM table WHERE (per_name LIKE '%a%' OR per_last_name LIKE '%a%') AND per_search_permissions="YES"

    You must remember that the order of execution in this case after the Where goes first the parentheses and then the AND, it has already selected all that its name and surname have an "a" and then proceeds to verify the permissions

    I hope the information is helpful

    • 0