Mysql NOT IN clause query does't return what I want

Question

I Think I do not understand something but i have a very strange result with not in.

I have this kind of tables

TABLE membres

idMembre
1
2
...

Table membres_has_domaines

idMembre  idDomaine
1         10
1         11
1         40
2         10
2         13
2         20
3         40
3         22

i run this query

select m.idMembre from membres m 
inner join membres_has_domaines md on m.idMembre=md.idMembre 
where md.idDomaine not in (40) 
group by m.idMembre

and the result retuns me

idMembre
1
2
3

the question is... why user 1 and 3 is in this result ?

thanks a lot for your help

EDIT : after first answer, i understand the problem... in reality my request is many more complex. I'm working on a full dynamic filter system generated by a HTML form where you choose your parameters... requests looks like that. here i want members with domain matching (1,2) excluding members with domain 27 and having country in (64, 4, 24)

select m.idMembre,  memberName, 
GROUP_CONCAT(distinct d.domaineName SEPARATOR ", ") as domaines , 
GROUP_CONCAT(distinct a.zipAdresse SEPARATOR ", ") as zips, 
GROUP_CONCAT(distinct p.countryName SEPARATOR ", ") as countryNames 
from membres
 m left join titres t on m.idTitre=t.idTitre 
left join civilites c on m.idCivilite=c.idCivilite 
left join adresses a on a.idMembre=m.idMembre left 
join pays p on a.idCountry =p.idCountry 
left join emails e on e.idMembre=m.idMembre 
left join membres_has_domaines md on md.idMembre=m.idMembre 
left join domaines d on md.idDomaine=d.idDomaine 
where 1=1 and md.idDomaine in (1, 2) 
and md.idDomaine not like '27' 
and a.idCountry in (64, 4, 24) 
group by m.idMembre

note ... this query don't do the job .. for the same reason of my original simplified example.

i don't know if i can do that without using having clause or subqueries ... it's quite complex. I think, the subqueries are the only way ? what do you think ? ...

Because you have records for all three users with entries different from 40 you can see it, if you use `Select * ` without ´` Group by` — Jens, Mar 02 '18 at 12:33
Because rows exists where `idMembre in (1, 3)` and `idDomaine not in (40)`. You are probably looking for `NOT EXISTS`. — HoneyBadger, Mar 02 '18 at 12:33

score 2 · Accepted Answer · answered Mar 02 '18 at 14:13

2

As others have said, you have many records for each member, and when at least one of the records matches your not in condition, it will appear in the result set.

One approach is to find all the members which do have the offending record, and then return the complement set:

SELECT 
    m.idMembre 
FROM 
    membres_has_domaines m
WHERE
    m.idMembre NOT IN (
        SELECT idMembre 
        FROM membres_has_domaines 
        WHERE idDomaine = 40
    );

answered Mar 02 '18 at 14:13

Peter Abolins

1,520
1
11
18

yes i think it could be the solution ... but if possible i would like to avoid generating subqueries ... because I'm on a full dynamic system and this is realy complex... for example i can have many params using the same linked tabes asked (see my original post update, and example). – J. Doe Mar 02 '18 at 14:43
again .. thank you. After burning my brain ... I've made modifications to my script to work like that ;) It seems it work now... but I must test many cases for ferifications. thanks for your help – J. Doe Mar 02 '18 at 16:03

Gordon Linoff · Answer 2 · 2018-03-02T12:47:27.480

1

They are in the result because your data has rows that match the not in condition.

If you want users that do not have "40" at all, then you need to consider all the rows as a group. Hence, think group by . . . along with a condition:

select md.idMembre
from membres_has_domaines md
where md.idDomaine not in (40) 
group by md.idMembre 
having sum( md.idDomaine in (40) ) = 0;

The having clause counts the number of rows for each member that match the condition. The = 0 says that no rows exist for the member.

Note that I removed the join to membres. Given your sample data and query, it is not needed for the query. If you want other columns, then it should be part of the query.

edited Mar 02 '18 at 12:47

answered Mar 02 '18 at 12:34

Gordon Linoff

1,242,037
58
646
786

Hello. thank you for your answer... I must think about how to implement that in my system. I've made a full dynamic filter system automatically generated by a form ... and in reality there are many more parameters that can be requested... The real example is I want to find members with domain in (1,2,3) excluding thoes who have domains in (4,5) ... – J. Doe Mar 02 '18 at 14:18
after tests. having will only work if i include the domain 40 in my where clause ... whiwh is not the case IRL for me ... :'( – J. Doe Mar 02 '18 at 15:13
@J.Doe . . . You should not edit your post, which invalidates that answers of people trying to help you. You should ask another question. – Gordon Linoff Mar 03 '18 at 02:58

score 1 · Answer 3 · answered Mar 02 '18 at 12:34

Because you also have records as:

idMembre  idDomaine
1         10
1         11
3         22

So before group by the statement you will have next result:

 idMembre  idDomaine
 1         10
 1         11
 2         10
 2         13
 2         20
 3         22

Mysql NOT IN clause query does't return what I want

3 Answers3