Problem with "NOT IN (subquery)

From: "Steve SAUTETNER" <steve(at)sautetner(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Problem with "NOT IN (subquery)
Date: 2005-11-13 06:27:09
Message-ID: PCEBIJLLLHHPALLADENFGECICDAA.steve@sautetner.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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 ...

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2005-11-13 09:16:18 Re: Problem with "NOT IN (subquery)
Previous Message Tom Lane 2005-11-11 23:27:12 Re: cli in sql?