X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id A257ED1C4EB for ; Wed, 30 Jul 2003 19:10:26 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 80568-05 for ; Wed, 30 Jul 2003 16:09:25 -0300 (ADT) Received: from jal.clueinc.net (xd84b436b.ip.e-nt.net [216.75.67.107]) by svr1.postgresql.org (Postfix) with ESMTP id 12118D1C4D7 for ; Wed, 30 Jul 2003 16:10:25 -0300 (ADT) Received: from jal.clueinc.net (localhost [127.0.0.1]) by jal.clueinc.net (8.12.9/8.12.9/Debian-5) with ESMTP id h6UK7WA9016671 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=FAIL) for ; Wed, 30 Jul 2003 15:07:33 -0500 Received: (from jal@localhost) by jal.clueinc.net (8.12.9/8.12.9/Debian-5) id h6UK7Wnn016669 for pgsql-sql@postgresql.org; Wed, 30 Jul 2003 15:07:32 -0500 Date: Wed, 30 Jul 2003 15:07:32 -0500 From: Jamie Lawrence To: pgsql-sql@postgresql.org Subject: Fwd: Bad Join moment - how is this happening? Message-ID: <20030730200732.GW1073@jal.clueinc.net> Mail-Followup-To: pgsql-sql@postgresql.org Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Organization: clue inc X-URL: http://www.clueinc.net/ User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/462 X-Sequence-Number: 14576 I fully admit that I've been staring at this too long, and simply don't understand what is wrong. Apologies aside, any kind sql hackers who care to look this over will earn my undying gratitude, and a beer in the bar of your choice, should we ever meet. General issue: I'm getting cartesean products instead of left joins, and I feel like a moron. I have two tables (more actually, but the result will fix all of them): dlm=3D# \d documents Table "public.documents" Column | Type | Modi= fiers=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 ---------------+-----------------------------+-----------------------------= ------------------------------ id | integer | not null default nextval('pu= blic.documents_id_seq'::text) projects_id | text |=20 doc_num | text |=20 description | text |=20 date | timestamp without time zone |=20 createdate | timestamp without time zone | default now() moddate | timestamp without time zone |=20 people_id | integer |=20 parent | integer |=20 document_type | integer |=20 state | integer |=20 machines_id | integer |=20 phases_id | integer |=20 Indexes: documents_id_pkey primary key btree (id), documents_parent_seq btree (parent), documents_people_id_seq btree (people_id), documents_projects_id btree (projects_id) Foreign Key constraints: phases_exists FOREIGN KEY (phases_id) REFERENCES p= hases(id) ON UPDATE NO ACTION ON DELETE NO ACTION, parent_exists FOREIGN KEY (parent) REFERENCES docu= ments(id) ON UPDATE NO ACTION ON DELETE NO ACTION, machine_exists FOREIGN KEY (machines_id) REFERENCE= S machines(id) ON UPDATE NO ACTION ON DELETE NO ACTION, people_exists FOREIGN KEY (people_id) REFERENCES p= eople(id) ON UPDATE NO ACTION ON DELETE NO ACTION, project_exists FOREIGN KEY (projects_id) REFERENCE= S projects(id) ON UPDATE NO ACTION ON DELETE NO ACTION Triggers: documents_timestamp_tr dlm=3D# \d d_addenda Table "public.d_addenda" Column | Type | Modifiers=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 -------------------+---------+---------------------------------------------= -------------- id | integer | not null default nextval('public.d_addenda_i= d_seq'::text) documents_id | integer |=20 item_num | text |=20 drawing_reference | text |=20 Indexes: d_addenda_id_pkey primary key btree (id), d_addenda_documents_id_idx btree (documents_id) Foreign Key constraints: documents_exists FOREIGN KEY (documents_id) REFERE= NCES documents(id) ON UPDATE NO ACTION ON DELETE NO ACTION I have a view: create or replace view addenda as select documents.id, documents.oid, documents.projects_id, documents.doc_num, documents.description, documents.date, documents.createdate, documents.moddate, documents.people_id, documents.parent, documents.document_type, documents.state, documents.machines_id, documents.phases_id, d_addenda.item_num, d_addenda.drawing_reference from d_addenda as a, documents as d where a.documents_id =3D d.id; I appear to be getting a cartesean product when I select against the view 'addenda', when I want a left inner join. That is, I want documents records matched to addenda records only when there is a record in d_addenda with a documents_id that matches the id field in documents. An example: dlm=3D# select * from documents; id | projects_id | doc_num | description | date = | createdate | moddate | peopl= e_id | parent | document_type | state | machines_id | phases_id=20 ----+-------------+---------+----------------------------+-----------------= ----------+----------------------------+----------------------------+------= -----+--------+---------------+-------+-------------+----------- 10 | 1 | | this is a test description | = | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | = | | | | |=20=20=20=20=20=20=20= =20=20=20 2 | 1 | foo123 | description text | 2003-07-27 19:03= :01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | = | | 1 | 1 | |=20=20=20=20=20=20=20= =20=20=20 (2 rows) dlm=3D# select * from d_addenda; id | documents_id | item_num | drawing_reference=20 ----+--------------+----------+------------------- 7 | 10 | 2 | none 2 | 2 | 1 | foo (2 rows) dlm=3D# select * from addenda; id | oid | projects_id | doc_num | description | = date | createdate | moddate = | people_id | parent | document_type | state | machines_id | phases_id | = item_num | drawing_reference=20 ----+--------+-------------+---------+----------------------------+--------= -------------------+----------------------------+--------------------------= --+-----------+--------+---------------+-------+-------------+-----------+-= ---------+------------------- 10 | 183371 | 1 | | this is a test description | = | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.09452= 1 | | | | | | | = 2 | none 10 | 183371 | 1 | | this is a test description | = | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.09452= 1 | | | | | | | = 1 | foo 2 | 180877 | 1 | foo123 | description text | 2003-07= -27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362= | | | 1 | 1 | | | = 2 | none 2 | 180877 | 1 | foo123 | description text | 2003-07= -27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362= | | | 1 | 1 | | | = 1 | foo 10 | 183371 | 1 | | this is a test description | = | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.09452= 1 | | | | | | | = 2 | none 10 | 183371 | 1 | | this is a test description | = | 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.09452= 1 | | | | | | | = 1 | foo 2 | 180877 | 1 | foo123 | description text | 2003-07= -27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362= | | | 1 | 1 | | | = 2 | none 2 | 180877 | 1 | foo123 | description text | 2003-07= -27 19:03:01.30362 | 2003-07-27 19:03:01.30362 | 2003-07-27 19:03:01.30362= | | | 1 | 1 | | | = 1 | foo (8 rows) I'm sure I'm being an idiot, but does anyone have a fix? Thanks. -j --=20 Jamie Lawrence jal@jal.org It it ain't broke, let me have a shot at it.