Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by hub.org (8.10.1/8.10.1) with ESMTP id e6SLicB25034 for ; Fri, 28 Jul 2000 17:44:38 -0400 (EDT) Received: by rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgresql.org; Fri, 28 Jul 2000 16:44:37 -0500 (CDT) Date: Fri, 28 Jul 2000 16:44:37 -0500 From: "Ross J. Reedstrom" To: pgsql-sql@postgresql.org Subject: Re: two table comparison: find a mismatch? Message-ID: <20000728164437.B7028@rice.edu> Mail-Followup-To: pgsql-sql@postgresql.org References: <3981F322.1CFE9A47@inter-resa.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0i In-Reply-To: <3981F322.1CFE9A47@inter-resa.com>; from web@inter-resa.com on Fri, Jul 28, 2000 at 04:54:58PM -0400 X-Archive-Number: 200007/276 On Fri, Jul 28, 2000 at 04:54:58PM -0400, Web Manager wrote: > Hello, > > here is to table: > Table "city" > Attribute | Type | Modifier > ----------------+-------------+--------------------------------------------- > city_id | smallint | not null default > nextval('seq_city'::text) > region_id | smallint | > region_tour_id | smallint | > country_id | smallint | > name_fr | varchar(40) | > name | varchar(40) | > url_id | smallint | > > > Table "region" > Attribute | Type | Modifier > -----------+-------------+---------------------------------------------- > region_id | smallint | not null default nextval('seq_region'::text) > country_id| smallint | > name_fr | varchar(40) | > name | varchar(40) | > address | boolean | > url_id | smallint | > > > I whant to fins a "hole" in the linkage... a region number in the city > table is not in the region table any more. > > How can I do that? > SELECT * FROM city WHERE region_id NOT IN (SELECT region_id FROM region) should do it. BTW, the syntax on your defaults for the id's seems a bit odd. Any reason your casting the text literal to text? Have we got a bug somewhere? Ross -- Ross J. Reedstrom, Ph.D., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005