NOT IN not working?

[sachavdk]

Hi,

I've got a query here:
[code]SELECT DISTINCT
g.group_id,
g.name,
g.maxmem maxmem
FROM
groups g,
(SELECT
COUNT(* ) num,
group_id
FROM usergroup
GROUP BY group_id) ug
WHERE
g.group_id NOT IN (ug.group_id);[/code]
When I remove the where, it gives me 3 results which is correct with my current rows)
When I set the were to [color=blue]g.group_id IN (ug.group_id)[/color], it gives me 2 results (also ok).
But when I use NOT IN (like the full query above) it gives me the 3 results as without the where.
But it should be 1 result. So where does it go wrong?

[btherl]

I think you should be joining on the group_id between the groups table and the "ug" subquery, rather than using "NOT IN".  That "NOT IN" will only check one single ug.group_id, rather than the whole set.

Try:[code=php:0]SELECT DISTINCT
g.group_id,
g.name,
g.maxmem maxmem
FROM
groups g
        LEFT JOIN
(SELECT
COUNT(* ) num,
group_id
FROM usergroup
GROUP BY group_id) ug
        ON g.group_id = ug.group_id
WHERE
ug.group_id IS NULL[/code]


The reason for "IS NULL" is that it will give you all rows not matching ug, which I think is what you want.  IS NOT NULL will give you what you wanted when you did "g.group_id IN (ug.group_id)".

Alternatively you can do:

[code=php:0]SELECT DISTINCT
g.group_id,
g.name,
g.maxmem maxmem
FROM
groups g
WHERE
g.group_id NOT IN (SELECT DISTINCT group_id FROM usergroup);[/code]


No need to get the count since you aren't using it.. and no need to select from ug when it doesn't appear in the results.

[sachavdk]

Guess it's working now :)
I'm gonna try it with other combinations.

But actually I need that count ;)
Not in the query (in my program) but I forgot to do an expression on the field and select it.

The SELECT should actually be
g.group_id,
g.name,
CONCAT((g.maxmem-gu.num), '/', g.maxmem) freeplaces

But thx for your help