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_st...@machinemanagement.com > 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 > > >
