X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 6FC0CD6D5A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 13 Nov 2005 02:27:18 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 07680-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 13 Nov 2005 06:27:16 +0000 (GMT)
X-Greylist: whitelisted by SQLgrey-
Received: from smtp6-g19.free.fr (smtp6-g19.free.fr [212.27.42.36])
	by svr1.postgresql.org (Postfix) with ESMTP id AC7CCD6D59
	for <pgsql-sql@postgresql.org>; Sun, 13 Nov 2005 02:27:15 -0400 (AST)
Received: from pegasus (tok69-1-82-67-36-133.fbx.proxad.net [82.67.36.133])
	by smtp6-g19.free.fr (Postfix) with SMTP id EF4DB9606
	for <pgsql-sql@postgresql.org>; Sun, 13 Nov 2005 07:27:15 +0100 (CET)
From: "Steve SAUTETNER" <steve@sautetner.com>
To: <pgsql-sql@postgresql.org>
Subject: Problem with "NOT IN (subquery)
Date: Sun, 13 Nov 2005 07:27:09 +0100
Message-ID: <PCEBIJLLLHHPALLADENFGECICDAA.steve@sautetner.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/110
X-Sequence-Number: 23361

Hi,

I have a table named "famille" whose structure and content is :

 famille_code | famille_mere_famille_code |  famille_libelle  |
famille_niveau
--------------+---------------------------+-------------------+-------------
---
 000000       |                           | Mhre              |
0
 I00001       | 000000                    | Composants        |
1
 IN0001       | I00001                    | Micro-processeurs |
2
 IN0002       | I00001                    | Mimoires RAM      |
2
 INS001       | IN0002                    | DDR-SDRAM         |
3
 INS002       | IN0002                    | DDR2-SDRAM        |
3
 INS003       | IN0002                    | SDR-SDRAM         |
3
 IN0003       | I00001                    | Cartes mhres      |
2
 IN0004       | I00001                    | Disques durs      |
2
 IN0005       | I00001                    | Cartes graphiques |
2
 IN0006       | I00001                    | Cartes son        |
2
 IN0007       | I00001                    | Riseau            |
2
 IN0008       | I00001                    | Lecteurs CD/DVD   |
2
 IN0009       | I00001                    | Graveurs CD/DVD   |
2
 IN0010       | I00001                    | Bontiers          |
2
 I00002       | 000000                    | Piriphiriques     |
1
 IN0011       | I00002                    | Cli USB           |
2
 IN0012       | I00002                    | Modems            |
2
 IN0013       | 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 ...