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=# \d documents
Table "public.documents"
Column | Type | Modifiers
---------------+-----------------------------+-----------------------------------------------------------
id | integer | not null default
nextval('public.documents_id_seq'::text)
projects_id | text |
doc_num | text |
description | text |
date | timestamp without time zone |
createdate | timestamp without time zone | default now()
moddate | timestamp without time zone |
people_id | integer |
parent | integer |
document_type | integer |
state | integer |
machines_id | integer |
phases_id | integer |
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 phases(id)
ON UPDATE NO ACTION ON DELETE NO ACTION,
parent_exists FOREIGN KEY (parent) REFERENCES documents(id)
ON UPDATE NO ACTION ON DELETE NO ACTION,
machine_exists FOREIGN KEY (machines_id) REFERENCES
machines(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
people_exists FOREIGN KEY (people_id) REFERENCES people(id)
ON UPDATE NO ACTION ON DELETE NO ACTION,
project_exists FOREIGN KEY (projects_id) REFERENCES
projects(id) ON UPDATE NO ACTION ON DELETE NO ACTION
Triggers: documents_timestamp_tr
dlm=# \d d_addenda
Table "public.d_addenda"
Column | Type | Modifiers
-------------------+---------+-----------------------------------------------------------
id | integer | not null default
nextval('public.d_addenda_id_seq'::text)
documents_id | integer |
item_num | text |
drawing_reference | text |
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) REFERENCES
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 = 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=# select * from documents;
id | projects_id | doc_num | description | date |
createdate | moddate | people_id | parent |
document_type | state | machines_id | phases_id
----+-------------+---------+----------------------------+---------------------------+----------------------------+----------------------------+-----------+--------+---------------+-------+-------------+-----------
10 | 1 | | this is a test description | |
2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | | |
| | |
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 | |
(2 rows)
dlm=# select * from d_addenda;
id | documents_id | item_num | drawing_reference
----+--------------+----------+-------------------
7 | 10 | 2 | none
2 | 2 | 1 | foo
(2 rows)
dlm=# 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
----+--------+-------------+---------+----------------------------+---------------------------+----------------------------+----------------------------+-----------+--------+---------------+-------+-------------+-----------+----------+-------------------
10 | 183371 | 1 | | this is a test description |
| 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | |
| | | | | 2 | none
10 | 183371 | 1 | | this is a test description |
| 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | |
| | | | | 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.094521 | |
| | | | | 2 | none
10 | 183371 | 1 | | this is a test description |
| 2003-07-30 12:22:48.094521 | 2003-07-30 12:22:48.094521 | |
| | | | | 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
--
Jamie Lawrence [EMAIL PROTECTED]
It it ain't broke, let me have a shot at it.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
