Thread: [GENERAL] Query Improvement??

Goal is to return all vendors which exist in all three companies

I think I got lucky figuring this out. Is there an obviously better way?

combined_item_master looks like this:
company_code character varying(10) NOT NULL,
primary_vendor_no character varying(7)
..more fields

data looks like this:

company_code | primary_vendor
AAA                                      003
BBB                                      004
CCC                                      001
CCC                                      004
AAA                                      123
BBB                                      123
CCC                                      123
BBB                                      003

Query returns all primary_vendor_no (as vendor_locations) which exist in all three companies
results:
vendor_locations
123


Here's the query

select primary_vendor_no, count(primary_vendor_no) as vendor_locations
from
(
SELECT distinct primary_vendor_no, company_code
  FROM combined_item_master
group by primary_vendor_no, company_code
) as a

group by primary_vendor_no
having count(primary_vendor_no)=3
order by vendor_locations DESC, primary_vendor_no


Thanks
Bret