Query help

From: Bret Stern <bret_stern(at)machinemanagement(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Query help
Date: 2016-10-05 03:31:05
Message-ID: 1475638265.3904.12.camel@centos65.machinemanagement.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2016-10-05 03:35:38 Re: Query help
Previous Message Michael Paquier 2016-10-05 00:50:03 Re: ZSON, PostgreSQL extension for compressing JSONB