CODEWITHSUNDEEP
mysqlsql
mecab dev
mecab dev

Reputation: 31

Exclude parent row if any child rows meet condition

I have two table category and products and I'm doing filtering with many methods on the rows using full string text and like.

This is my data :

Table Category :

Id  Libelle   Description
1   Animals   dogs
2   Kids      games

Table Product :

Id  Name   Category_Id
1   aaa kkk   2
2   bbb kkk   2

There is a FK between these two tables.

This is my first query I select rows and then I want to exclude the row if category.Libelle OR Category.Description match some word :

    select distinct * from category cat JOIN product pro on pro.Category_Id = cat.Id
where (MATCH (pro.Name) AGAINST( 'kkk'  IN BOOLEAN MODE)) 
AND
(concat(cat.Libelle , cat.Description) NOT REGEXP '[[:<:]]kids[[:>:]]')

Here the result is not correct it returns 2 rows except that must return 0 rows why ?

The second query I want the same purpose but I will check with product.Name :

    select distinct * from category cat JOIN product pro on pro.Category_Id = cat.Id
where (MATCH (pro.Name) AGAINST( 'kkk'  IN BOOLEAN MODE)) 
AND
pro.Name NOT REGEXP '[[:<:]]bbb[[:>:]]'

Here I have as result one row that is it :

'2', 'kids', 'games', '1', 'aaa kkk', '2'

But I want to exclude that row also, so I want exclude the row of the category if any of the Name of list Product pro.Name associated with Category_Id contains exact word 'bbb', So I want a result of 0 row and not 1 row, How can I do it ?

Upvotes: 0

Views: 220

Answers (1)

user18098820
user18098820

Reputation:

I've modified your regex pattern, and replaced the MATCH function with LIKE.
Note that MySQL using standard regex since version 8. I have used the following elements in the regex pattern:
^ beginning of line
Kids literal string
$ end of Line
You may want to use
\b beginning or end of line (has to be escaped and becomes \\b)
[Kk]ids matches Kids and kids.

create Table Category (
Id int,
Libelle   varchar(10),
Description varchar(10));
insert into Category values
(1,'Animals','dogs'),
(2,'Kids','games'),
(3,'Kids plus','clothes');

create Table Product (
Id int,
Name varchar(10),
Category_Id int);
insert into Product values
(1   ,'aaa kkk',   2),
(2   ,'bbb kkk',   2),
(3   ,'bbb kkk',   1),
(4   ,'bbb kkj',   1),
(5   ,'bbb kkk',   3);

select 
  p.Id,p.Name, 
  c.Id,c.Libelle,c.Description
from Category c 
JOIN Product p on p.Category_Id = c.Id
where p.Name LIKE '%kkk%'  
AND
c.Libelle NOT REGEXP '^Kids$'
AND
c.Description NOT REGEXP '^Kids$'

Id | Name    | Id | Libelle   | Description
-: | :------ | -: | :-------- | :----------
 3 | bbb kkk |  1 | Animals   | dogs       
 5 | bbb kkk |  3 | Kids plus | clothes

db<>fiddle here

Upvotes: 1

Related Questions