A JOIN question

Started by stanover 6 years ago3 messagesgeneral
Jump to latest
#1stan
stanb@panix.com

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

#2Michael Lewis
mlewis@entrata.com
In reply to: stan (#1)
Re: A JOIN question

LEFT join mfg_vendor_relationship on
mfg_vendor_relationship.mfg_key = mfg_part.mfg_key
AND
mfg_vendor_relationship.project_key = bom_item.project_key
LEFT join vendor on
mfg_vendor_relationship.vendor_key = vendor.vendor_key

Perhaps I am missing something, but it seems like just a matter of changing
inner join to left so you keep what you already have and augment vendor
information when it exists. I never use right joins (I re-write to always
declare as left) so I am not sure if the right join near the top screws
that up. I might consider using a UNION ALL to combine sets where values
are NULL with left joins, and use plain (inner) joins for the not nulls.

#3stan
stanb@panix.com
In reply to: Michael Lewis (#2)
Re: A JOIN question

On Mon, Dec 30, 2019 at 02:47:53PM -0700, Michael Lewis wrote:

LEFT join mfg_vendor_relationship on
mfg_vendor_relationship.mfg_key = mfg_part.mfg_key
AND
mfg_vendor_relationship.project_key = bom_item.project_key
LEFT join vendor on
mfg_vendor_relationship.vendor_key = vendor.vendor_key

Perhaps I am missing something, but it seems like just a matter of changing
inner join to left so you keep what you already have and augment vendor
information when it exists. I never use right joins (I re-write to always
declare as left) so I am not sure if the right join near the top screws
that up. I might consider using a UNION ALL to combine sets where values
are NULL with left joins, and use plain (inner) joins for the not nulls.

Thanks. That looks like it gives me what I need.

RE right joins, I just got started doing that, and it is easier for me to
think that way.

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