Probably a newbie question

[stan <stanb@xxxxxxxxx>]

Sorry, I got the list address wrong the first time, and when I corected it,
I forget to fix the subject line.

I apologize for asking, what I suspect will turn out to be a newbie
question, but I have managed to get myself quite confused on this.

I am defining a view as follows


CREATE OR REPLACE view purchase_view as 
select 
	project.proj_no ,
	qty ,
	mfg_part.mfg_part_no ,
	mfg.name as m_name ,
	mfg_part.descrip as description ,
	( 
	SELECT 
		name
	FROM
		vendor
	WHERE
		bom_item.vendor_key =
		(
			SELECT
				vendor_key
			FROM
				mfg_vendor_relationship
			WHERE
				bom_item.mfg_key = mfg_key
			AND
				prefered = TRUE
			AND
				bom_item.project_key = project_key
				
		)
	) 
	as v_name ,
	/*
	vendor.name as v_name ,
	*/
	cost_per_unit ,
	costing_unit.unit,
	need_date ,
	order_date ,
	recieved_date ,
	po_no ,
	po_line_item 
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 vendor on 
	vendor.vendor_key = bom_item.vendor_key
inner join costing_unit on 
	costing_unit.costing_unit_key = bom_item.costing_unit_key
inner join mfg on 
	mfg.mfg_key = bom_item.mfg_key 
WHERE bom_item is NOT NULL  
ORDER BY 
	project.proj_no ,
	mfg_part
	;

Most of the tables are pretty much simple key -> value relationships for
normalization. I can add the create statements to this thread if it adds
clarity.

The exception is:



CREATE TABLE mfg_vendor_relationship (
    mfg_vendor_relationship_key_serial         integer DEFAULT nextval('mfg_vendor_relationship_key_serial')
    PRIMARY KEY ,
    mfg_key       integer NOT NULL,
    vendor_key    integer NOT NULL,
    project_key   integer NOT NULL,
    prefered      boolean NOT NULL ,
    modtime           timestamptz DEFAULT current_timestamp ,
    FOREIGN KEY (mfg_key) references mfg(mfg_key) ,
    FOREIGN KEY (vendor_key) references vendor(vendor_key) ,
    FOREIGN KEY (project_key) references project(project_key) ,
    CONSTRAINT mfg_vendor_constraint 
                UNIQUE (
			mfg_key , 
			vendor_key , 
			project_key
		)
);


I am down to having a single row in the mfg_vendor_relationship as follows:

 mfg_vendor_relationship_key_serial | mfg_key | vendor_key | project_key |
 prefered |            modtime            
 ------------------------------------+---------+------------+-------------+----------+-------------------------------
				 164 |       1 |          1 |           2 |
				 t        | 2019-08-10 14:21:04.896619-04

But trying to do a select * from this view returns:

ERROR:  more than one row returned by a subquery used as an expression

Can someone please enlighten me as to the error of my ways?


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


----- End forwarded message -----

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



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