Good day. Faced with an incomprehensible error in writing the repository for selecting all clients that are assigned to the specified addresses.

CREATE DEFINER=`grasa_db`@`%` PROCEDURE `GetClients`( likeParam NVARCHAR(50), pageSizeParam INT, pageIndexParam INT, idPersonnelParam CHAR(36)) BEGIN DECLARE startIndex INT; SET startIndex = pageSizeParam * pageIndexParam; SELECT Id, FirstName, LastName, MiddleName FROM client WHERE (client.IdAddress IN (SELECT personneladdress.IdAddress FROM personneladdress WHERE personneladdress.IdPersonnel = idPersonnelParam) AND FirstName Like(CASE WHEN likeParam IS NULL THEN client.FirstName ELSE concat('%', likeParam, '%') END) OR LastName Like(CASE WHEN likeParam IS NULL THEN client.LastName ELSE concat('%', likeParam, '%') END) OR MiddleName Like(CASE WHEN likeParam IS NULL THEN client.MiddleName ELSE concat('%', likeParam, '%') END)) LIMIT startIndex, pageSizeParam; END

So here is the essence: by transferring the staff ID, I get a list of addresses for which it is assigned. Next, select clients that are assigned to any of the selected address. Everything seems to be working, but when a check for a search by name is added, all clients are displayed at all addresses, even for those who are not assigned to the specified personnel.

Closed due to the fact that off-topic participants Alexey Shimansky , Abyx , cheops , aleksandr barakin , user194374 Aug 12 '16 at 5:53 .

It seems that this question does not correspond to the subject of the site. Those who voted to close it indicated the following reason:

  • "The question is caused by a problem that is no longer reproduced or typed . Although similar questions may be relevant on this site, solving this question is unlikely to help future visitors. You can usually avoid similar questions by writing and researching a minimum program to reproduce the problem before publishing the question. " - Alexey Shimansky, Abyx, cheops, aleksandr barakin, Community Spirit
If the question can be reformulated according to the rules set out in the certificate , edit it .

    1 answer 1

    I apologize, it was necessary to refresh my head) The whole problem is in the bracket, which I did not put there

     client.IdAddress IN (SELECT personneladdress.IdAddress FROM personneladdress WHERE personneladdress.IdPersonnel = idPersonnelParam) AND (FirstName Like(CASE WHEN likeParam IS NULL THEN client.FirstName ELSE concat('%', likeParam, '%') END) OR LastName Like(CASE WHEN likeParam IS NULL THEN client.LastName ELSE concat('%', likeParam, '%') END) OR MiddleName Like(CASE WHEN likeParam IS NULL THEN client.MiddleName ELSE concat('%', likeParam, '%') END))