From: Christian Paul B. Cosinas <cpc@cybees.com>
To: 'Steve SAUTETNER' <steve@sautetner.com>
To: pgsql-sql@postgresql.org
Subject: Re: Problem with "NOT IN (subquery)
Date: Mon, 14 Nov 2005 09:47:12 -0000
Message-ID: <000f01c5e900$63c2a6a0$1e21100a@ghwk02002147> (raw)
In-Reply-To: <PCEBIJLLLHHPALLADENFGECICDAA.steve@sautetner.com>
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 |
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 ...
---------------------------(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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cpc@cybees.com, steve@sautetner.com
Subject: Re: Problem with "NOT IN (subquery)
In-Reply-To: <000f01c5e900$63c2a6a0$1e21100a@ghwk02002147>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox