CODEWITHSUNDEEP
mysqljpaspring-data-jpa
user10098815
user10098815

Reputation:

MySQL LIKE multiple fields condition

I just want to start out by saying that I am new to MySql, so I might be doing something really stupid here. I want to make a search by 3 fields, here is my query:

@Query( value = "SELECT from Parent p WHERE p.login LIKE :search OR p.firstName LIKE :search OR p.lastName LIKE :search AND p.school = :school AND p.status = :status")

As you can see I want to search by 3 fields: firstName, lastName and login. I also added some other conditions like I want it from a particular School and Status.

The search with the 3 fields work, let's say I write "Sarah" in the search and I get back 5 users the problem is that for example if I select in the filter the status = "Confirmed" - looks like the mysql doesn't care about this and still returns all the users with the string "sarah" in it despite their school or status conditions. What am I doing wrong here?

If I use only one Like - ex. only firstName - everything works perfectly... is it possible to use 3 with the conditions?

Upvotes: 1

Views: 82

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

Your OR clause should be wrapped by () so the AND condition can work correctly 

@Query( value = "SELECT * 
            from Parent p 
            WHERE ( p.login LIKE :search 
            OR p.firstName LIKE :search 
            OR p.lastName LIKE :search ) 
            AND p.school = :school AND p.status = :status")

Upvotes: 1

Related Questions