Faculty Faculty - 1 month ago 18
MySQL Question

SQL NOT LIKE with JOIN

I'm using MYSQL and can't get a NOT LIKE working with multiple JOINS.
I have 3 tables which look like this:

Parents (Table1):

+------------+-------+-----+
| ParentID | Name | AGE |
+------------+-------+-----+
| 1 | Peter | 26 |
| 2 | Karl | 33 |
| 3 | Tessa | 43 |
+------------+-------+-----+


Kids (Table2):

+------------+-------+-----+
| KidID | Name | AGE |
+------------+-------+-----+
| 1 | Mike | 3 |
| 2 | Mike | 13 |
| 3 | Jenna | 4 |
| 4 | Jessi | 14 |
+------------+-------+-----+


Parents_Kids (Table3):

+-----------+-------+
| ParentID | KidID |
+-----------+-------+
| 1 | 2 |
| 1 | 4 |
| 2 | 1 |
| 3 | 3 |
+-----------+-------+


Now i want to get all parent names who don't have a kid named Mike or any form of Mike in there name.

I tried this:

SELECT p.name
FROM PARENTS p JOIN
Parents_Kids pk
ON pk.ParentID = p.ParentID JOIN
Kids k
ON k.KidID = pk.KidID
WHERE k.Name NOT LIKE '%mike%';


But the result is wrong with this query.
If i try this query with LIKE it works correctly but not with NOT LIKE.

vkp vkp
Answer

I think you don't want to select the parent if atleast one of his/her kids have a name like mike. You can use having to filter such cases.

SELECT p.name 
FROM PARENTS p 
JOIN Parents_Kids pk ON pk.ParentID=p.ParentID 
JOIN Kids k ON k.KidID=pk.KidID 
group by p.name
having count(case when k.name like '%mike%' then 1 end) = 0
Comments