Try this:
select distinct vendor_no, vendor_name
from ap_vendors
where vendor_no in (
select vendor_no from ap_vendors
group by vendor_no
having array_agg(company_code) @> ARRAY['BUR','EBC','SNJ'])
On Wed, Oct 5, 2016 at 1:31 PM, Bret Stern <bret_stern@xxxxxxxxxxxxxxxxxxxxx> wrote:
Good evening,
I'm curious about a way to ask the following question of my vendors
table.
psuedo1 "select all vendors which exist in BUR and EBC and SNJ"
and
psuedo2 "select all vendors which DO NOT exist in all three show rooms
The data is from a Sage accounting system which I pull out and place in a
pg db. What we are trying to do is identify which vendors are defined in
all three of our showrooms, and vice-versa, which ones are not.
ap_vendors table
company_code character varying(10) NOT NULL,
ap_division_no character varying(2) NOT NULL,
vendor_no character varying(7) NOT NULL,
terms_code character varying(2),
vendor_name character varying(30),
address_line1 character varying(30),
address_line2 character varying(30),
address_line3 character varying(30),
city character varying(20),
state character varying(2),
zip_code character varying(10),
telephone_no character varying(17),
fax_no character varying(17),
CONSTRAINT ap_vendors_pk PRIMARY KEY (company_code, ap_division_no, vendor_no)
sample records:
"BUR";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''"
"BUR";"00";"AGORA";"30";"Agora Natural Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''"
"BUR";"00";"AKDOP";"30";"AKDO Pacific";"''";"''";"''";"''";"''";"94545";"''";"''"
"EBC";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''"
"EBC";"00";"AGORA";"30";"Agora Natural Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''"
"EBC";"00";"ARIZ01";"30";"Arizona Tile";"''";"''";"''";"''";"''" ;"94550";"''";"''"
"SNJ";"00";"AKDOP";"30";"AKDO Pacific";"''";"''";"''";"''";"''";"94545";"''";"''"
"SNJ";"00";"AGORA";"30";"Agora Natural Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''"
"SNJ";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''"
What I need is a query which I can modify to return only vendors which exists
in all three company_code columns ( BUR, EBC, SNJ) (they represent showroom location)
eg; exists in BUR, EBC, SNJ
ADEXU
AGORA
OR
modify the query to return only the vendors which DO NOT exist in all
three showrooms based on the first column company_code
eg;
AKDOP only exists in BUR and SNJ
ARIZ01 only exists in EBC
Thanks
Bret
