Re: [GENERAL] Finding missing records
am 27.01.2006, um 14:59:47 +0100 mailte Stefano B. folgendes: How can I find the difference between the two tables? table1 has 1 records table2 has 9900 records (these records are in table1 as well) I'd like to find 100 missing records. I have try this query select f1,f2,f3,f4 from table1 where (f1,f2,f3,f4) NOT IN (select f1,f2,f3,f4 from table2) select f1,f2,f3,f4 from table1 except select f1,f2,f3,f4 from table2; HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Finding missing records
select f1,f2,f3,f4 from table1 EXCEPT ALL select f1,f2,f3,f4 from table2 http://www.postgresql.org/docs/8.1/static/sql-select.html On 1/27/06, Stefano B. [EMAIL PROTECTED] wrote: Hi, I have two identical tables table1 (f1,f2,f3,f4 primary key (f1,f2,f3,f4)) table2 (g1,g2,g3,g4 primary key (g1,g2,g3,g4)) How can I find the difference between the two tables? table1 has 1 records table2 has 9900 records (these records are in table1 as well) I'd like to find 100 missing records. I have try this query select f1,f2,f3,f4 from table1 where (f1,f2,f3,f4) NOT IN (select f1,f2,f3,f4 from table2) but it seems not work (as I want). It returns me no records. If I use the IN clause it returns me all 1 table1 records. Thanks in advance Stefano ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Finding missing records
On Jan 27, 2006, at 08:59, Stefano B. wrote: select f1,f2,f3,f4 from table1 where (f1,f2,f3,f4) NOT IN (select f1,f2,f3,f4 from table2) but it seems not work (as I want). It returns me no records. If I use the IN clause it returns me all 1 table1 records. The standard way to do this is: select f1,f2,f3,f4 from table1 except select f1,f2,f3,f4 from table2; Note that IN and EXCEPT are essentially set operators - if you have duplicates in either table, you might not get what you expect. Your last comment above seems to indicate that this is indeed the case. If what you want is the =bag= difference of the two tables, you'll have to do something more complicated. Possible solutions might involve counting duplicates in both tables with a COUNT(*) and GROUP BY, and then joining on the four columns and subtracting the counts. - John D. Burger MITRE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Finding missing records
I wrote: Note that IN and EXCEPT are essentially set operators - if you have duplicates in either table, you might not get what you expect. If what you want is the =bag= difference of the two tables, you'll have to do something more complicated. and then I immediately saw Pandurangan's message indicating that ALL can be used to turn the set operators into bag operators, e.g., EXCEPT ALL. Cool! (And not complicated at all.) - John D. Burger MITRE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Finding missing records
On Fri, 27 Jan 2006, Stefano B. wrote: Hi, I have two identical tables table1 (f1,f2,f3,f4 primary key (f1,f2,f3,f4)) table2 (g1,g2,g3,g4 primary key (g1,g2,g3,g4)) How can I find the difference between the two tables? table1 has 1 records table2 has 9900 records (these records are in table1 as well) I'd like to find 100 missing records. I have try this query select f1,f2,f3,f4 from table1 where (f1,f2,f3,f4) NOT IN (select f1,f2,f3,f4 from table2) Is there a reason you've used f1-f4 in the table2 subselect rather than g1-g4? From the definitions above, I think the f1-f4 in the subselect are becoming outer references which isn't what you want. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq