Thread: Problem with "NOT IN (subquery)

Problem with "NOT IN (subquery)

From
"Steve SAUTETNER"
Date:
Hi,

I have a table named "famille" whose structure and content is :
famille_code | famille_mere_famille_code |  famille_libelle  |
famille_niveau
--------------+---------------------------+-------------------+-------------
---000000       |                           | Mhre              |
0I00001       | 000000                    | Composants        |
1IN0001       | I00001                    | Micro-processeurs |
2IN0002       | I00001                    | Mimoires RAM      |
2INS001       | IN0002                    | DDR-SDRAM         |
3INS002       | IN0002                    | DDR2-SDRAM        |
3INS003       | IN0002                    | SDR-SDRAM         |
3IN0003       | I00001                    | Cartes mhres      |
2IN0004       | I00001                    | Disques durs      |
2IN0005       | I00001                    | Cartes graphiques |
2IN0006       | I00001                    | Cartes son        |
2IN0007       | I00001                    | Riseau            |
2IN0008       | I00001                    | Lecteurs CD/DVD   |
2IN0009       | I00001                    | Graveurs CD/DVD   |
2IN0010       | I00001                    | Bontiers          |
2I00002       | 000000                    | Piriphiriques     |
1IN0011       | I00002                    | Cli USB           |
2IN0012       | I00002                    | Modems            |
2IN0013       | I00002                    | Imprimantes       |
2
(19 rows)

The first col is the family id and the second is the mother family id.
I would get a list of all families that are never in the col n°2, so
the families that aren't node but leaf.

The query, i made was "SELECT * FROM famille WHERE famille_code NOT IN
(SELECT DISTINCT famille_mere_famille_code FROM famille);"

But the DB returns 0 records instead of 15. If i use a list instead of a
subquery it works normaly but it's not easy to manage it like this.

So if anyone can help me please ...



Re: Problem with "NOT IN (subquery)

From
Stephan Szabo
Date:
On Sun, 13 Nov 2005, Steve SAUTETNER wrote:

> Hi,
>
> I have a table named "famille" whose structure and content is :
>
>  famille_code | famille_mere_famille_code |  famille_libelle  |
> famille_niveau
> --------------+---------------------------+-------------------+-------------
> ---
>  000000       |                           | Mhre              |

Is that a NULL famille_mere_famille_code?

> The first col is the family id and the second is the mother family id.
> I would get a list of all families that are never in the col n�2, so
> the families that aren't node but leaf.
>
> The query, i made was "SELECT * FROM famille WHERE famille_code NOT IN
> (SELECT DISTINCT famille_mere_famille_code FROM famille);"
>
> But the DB returns 0 records instead of 15. If i use a list instead of a
> subquery it works normaly but it's not easy to manage it like this.
>
> So if anyone can help me please ...

In the case where the subselect returns a NULL, the behavior of IN and NOT
IN is rather unfortunate. A NOT IN B is basically NOT(A IN B) and A IN B
is basically A =ANY B IIRC. However, A=ANY B only returns false if A = Bi
returns false for all Bi contained in B and A = NULL returns unknown, not
false, so NOT IN cannot return true if the subselect contains a NULL.

If that is a null above, probably the best solution is to exclude NULLs
from the subselect results.


Re: Problem with "NOT IN (subquery) - use NOT EXISTS

From
chester c young
Date:
--- Steve SAUTETNER <steve@sautetner.com> wrote:
> SELECT * FROM famille WHERE famille_code NOT IN
> (SELECT DISTINCT famille_mere_famille_code FROM famille);"

try
select * from famille f1 where not exists
(select 1 from famille f2 where f1.famille_code = f2.famille_mere_famille_code);

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 


Re: Problem with "NOT IN (subquery)

From
"Christian Paul B. Cosinas"
Date:
I think that query should work. Hmmm... Weird...
I tried that in my database and it is working.

What do you mean by this? " If i use a list instead of a subquery it works
normaly but it's not easy to manage it like this."

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Steve SAUTETNER
Sent: Sunday, November 13, 2005 6:27 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Problem with "NOT IN (subquery)

Hi,

I have a table named "famille" whose structure and content is :
famille_code | famille_mere_famille_code |  famille_libelle  |
famille_niveau
--------------+---------------------------+-------------------+---------
--------------+---------------------------+-------------------+----
---000000       |                           | Mhre              |
0I00001       | 000000                    | Composants        |
1IN0001       | I00001                    | Micro-processeurs |
2IN0002       | I00001                    | Mimoires RAM      |
2INS001       | IN0002                    | DDR-SDRAM         |
3INS002       | IN0002                    | DDR2-SDRAM        |
3INS003       | IN0002                    | SDR-SDRAM         |
3IN0003       | I00001                    | Cartes mhres      |
2IN0004       | I00001                    | Disques durs      |
2IN0005       | I00001                    | Cartes graphiques |
2IN0006       | I00001                    | Cartes son        |
2IN0007       | I00001                    | Riseau            |
2IN0008       | I00001                    | Lecteurs CD/DVD   |
2IN0009       | I00001                    | Graveurs CD/DVD   |
2IN0010       | I00001                    | Bontiers          |
2I00002       | 000000                    | Piriphiriques     |
1IN0011       | I00002                    | Cli USB           |
2IN0012       | I00002                    | Modems            |
2IN0013       | I00002                    | Imprimantes       |
2
(19 rows)

The first col is the family id and the second is the mother family id.
I would get a list of all families that are never in the col n°2, so the
families that aren't node but leaf.

The query, i made was "SELECT * FROM famille WHERE famille_code NOT IN
(SELECT DISTINCT famille_mere_famille_code FROM famille);"

But the DB returns 0 records instead of 15. If i use a list instead of a
subquery it works normaly but it's not easy to manage it like this.

So if anyone can help me please ...


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster


I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html