Re: [SQL] Problem with "NOT IN (subquery)
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 > --+---+---+- > --- > 00 | | 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Problem with "NOT IN (subquery) - use NOT EXISTS
--- Steve SAUTETNER <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem with "NOT IN (subquery)
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 --+---+---+- --+---+---+ --- 00 | | Mhre | 0 I1 | 00| Composants| 1 IN0001 | I1| Micro-processeurs | 2 IN0002 | I1| Mimoires RAM | 2 INS001 | IN0002| DDR-SDRAM | 3 INS002 | IN0002| DDR2-SDRAM| 3 INS003 | IN0002| SDR-SDRAM | 3 IN0003 | I1| Cartes mhres | 2 IN0004 | I1| Disques durs | 2 IN0005 | I1| Cartes graphiques | 2 IN0006 | I1| Cartes son| 2 IN0007 | I1| Riseau| 2 IN0008 | I1| Lecteurs CD/DVD | 2 IN0009 | I1| Graveurs CD/DVD | 2 IN0010 | I1| Bontiers | 2 I2 | 00| Piriphiriques | 1 IN0011 | I2| Cli USB | 2 IN0012 | I2| Modems| 2 IN0013 | I2| 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] selective dump pg_dump: only specific non-tables? with schema name?
Hi folks, Three questions about pg_dump (that might be a feature request). Let me know if pg_dump is not the best tool for this sort of thing. 1. Can I pg_dump only non-table information from the command line e.g. single function, all functions, trigger definitions? 2. Can I pg_dump schema objects that include the schema prefix "inline" e.g. CREATE FUNCTION "fred"."func1" 3. Can I pg_dump "CREATE OR REPLACE" rather than straight "CREATE", especially for functions? -- David T. Bath [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
