[GENERAL] Finding missing records

[Stefano B.]

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

Re: [GENERAL] Finding missing records

[A. Kretschmer]

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

[Pandurangan R S]

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

[John D. Burger]

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

[John D. Burger]

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

[Stephan Szabo]

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