Re: Problem with "NOT IN (subquery) - Mailing list pgsql-sql

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