A JOIN question

[stan <stanb@xxxxxxxxx>]

I am working on a system whee one group of folks inputs portions of data
into a table, and a 2nd completes the data for each row.


The engineers enter in the items they need into a BOM table, and purchasing
agents get quotes and create PO's. There is not a fixed relationship between
the parts (part number and manufacturers) and the vendor (mfg agent or
distributor),  A project manager determines which vendor to buy various
manufacturers items from. This sis controlled by the following table:



CREATE TABLE mfg_vendor_relationship (
    mfg_vendor_relationship_key_serial         integer DEFAULT nextval('ica.mfg_vendor_relationship_key_serial')
    PRIMARY KEY ,
    mfg_key       integer NOT NULL,
    vendor_key    integer NOT NULL,
    project_key   integer NOT NULL,
    modtime       timestamptz NOT NULL DEFAULT current_timestamp ,
    FOREIGN KEY (mfg_key) references mfg(mfg_key) ON DELETE RESTRICT ,
    FOREIGN KEY (vendor_key) references vendor(vendor_key) ON DELETE RESTRICT ,
    FOREIGN KEY (project_key) references project(project_key) ON DELETE RESTRICT ,
    CONSTRAINT mfg_vendor_constraint 
                UNIQUE (
			mfg_key , 
			vendor_key , 
			project_key
		)
);
So, I have the following view for the purchasing agents.


CREATE view t1 as 
select 
	project.proj_no ,
	qty ,
	costing_unit.unit,
	mfg_part.mfg_part_no ,
	mfg.name as m_name ,
	mfg_part.descrip as description ,
	vendor.name as v_name ,
	format_phone_no(
			vendor.area_code,
			vendor.phone_exchange,
			vendor.phone_number,
			vendor.phone_extension) office_phone ,
	cast(cost_per_unit as money) ,
	cast(qty * cost_per_unit as   money) line_ttl ,
	need_date ,
	order_date ,
	received_date ,
	po_no ,
	po_line_item , 
        po_revision ,
        po_rev_date ,
	po_terms_and_conditions.net_interval ,
	po_terms_and_conditions.discount_interval ,
	po_terms_and_conditions.discount
from 
	bom_item 
right join project on 
	project.project_key = bom_item.project_key
inner join mfg_part on 
	mfg_part.mfg_part_key = bom_item.mfg_part_key
inner join costing_unit on 
	costing_unit.costing_unit_key = bom_item.costing_unit_key
inner join mfg on 
	mfg.mfg_key = mfg_part.mfg_key 
inner join mfg_vendor_relationship on
	mfg_vendor_relationship.mfg_key = mfg_part.mfg_key
	AND
	mfg_vendor_relationship.project_key = bom_item.project_key
inner join vendor on
	mfg_vendor_relationship.vendor_key = vendor.vendor_key
FULL OUTER JOIN po_terms_and_conditions ON
	po_terms_and_conditions.po_terms_and_conditions_key = bom_item.po_terms_and_conditions_key
	;

Now, my problem is that this join:

inner join mfg_vendor_relationship on
	mfg_vendor_relationship.mfg_key = mfg_part.mfg_key
	AND
	mfg_vendor_relationship.project_key = bom_item.project_key

Means that the item to be purchased is invisible to the purchasing agent if
the project manager has not populated the cross reference table.

How can I modify this select so that all items for a given project will
appear in the purchasing agents view?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
						-- Benjamin Franklin