> On Oct 4, 2016, at 9: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 > > Not sure I like the schema but select vendor_no, count(*) from ap_vendors having count(*) = 3; and maybe count(*) < 3 is your second answer. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
