Fwd: Bad Join moment - how is this happening? - Mailing list pgsql-sql

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
withouttime 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_seqbtree (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
NOACTION,                        machine_exists FOREIGN KEY (machines_id) REFERENCES machines(id) ON UPDATE NO ACTION
ONDELETE NO ACTION,                        people_exists FOREIGN KEY (people_id) REFERENCES people(id) ON UPDATE NO
ACTIONON DELETE NO ACTION,                        project_exists FOREIGN KEY (projects_id) REFERENCES projects(id) ON
UPDATENO ACTION ON DELETE NO ACTION 
Triggers: documents_timestamp_tr


dlm=# \d d_addenda                               Table "public.d_addenda"     Column       |  Type   |
      Modifiers                          
-------------------+---------+-----------------------------------------------------------id                | integer |
notnull 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
DELETENO 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-3012: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-3012:22:48.094521 |           |        |               |       |             |           | 2        | none10 |
183371| 1           |         | this is a test description |                           | 2003-07-30 12:22:48.094521 |
2003-07-3012: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-2719: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-2719:03:01.30362  |           |        |             1 |     1 |             |           | 1        | foo10 |
183371| 1           |         | this is a test description |                           | 2003-07-30 12:22:48.094521 |
2003-07-3012:22:48.094521 |           |        |               |       |             |           | 2        | none10 |
183371| 1           |         | this is a test description |                           | 2003-07-30 12:22:48.094521 |
2003-07-3012: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-2719: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-2719: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                                        jal@jal.org
It it ain't broke, let me have a shot at it.