Why is my SQL query ignoring the university filter, and how can I fix it?

Troubleshooting a SQL Query: University Filter Issue

This SQL query targets the "people" table, using multiple filters to refine results. The problem is that the query returns records that shouldn't pass the university filter. This stems from operator precedence issues within the WHERE clause.

The original query suffers from incorrect application of AND and OR operators. The higher precedence of AND causes the university='2' condition to be incorrectly linked only to the MATCH operation. The fname and lname filters are evaluated independently, leading to unintended results.

The solution involves restructuring the query to enforce the correct order of operations:

SELECT *
FROM people
WHERE
    university='2'
    AND (
        MATCH (lname,fname) AGAINST ('+massive' IN BOOLEAN MODE)
        OR fname LIKE '%box%'
        OR lname LIKE '%box%'
    );

By grouping the fname and lname filters using parentheses, we ensure that the university='2' condition applies to all three criteria. This guarantees that only records from university '2' satisfying at least one of the name-based conditions are included in the output.

The above is the detailed content of Why is my SQL query ignoring the university filter, and how can I fix it?. For more information, please follow other related articles on the PHP Chinese website!