How to count a foreign key and use it to sort the primary table?

I have 2 tables:

Product (prod_id, prod_name, vend_id)
Vendor (vend_id, vend_name, vend_state)

I am trying to make a query that will give me a list of the vend_name for every vendor that supplied only one product. vend_id is a foreign key in Product, and I would like to count how many instances of each vend_id are in Product and then list out the vend_name of those that only occur once. Any help would be appreciated.

I am using Oracle SQL Developer (because I have to).

This is what I have, but keep getting either “invalid identifier” or “group function is not allowed here”

select count(*), Product.vend_id, Vendor.vend_id 
from Product 
inner join Vendor on Product.vend_id = vend.vend_id 
where count(*) < 2 
group by product.vend_id, vendor.vend_name;

​x
 
select count(*), Product.vend_id, Vendor.vend_id from Product inner join Vendor on Product.vend_id = vend.vend_id where count(*) < 2 group by product.vend_id, vendor.vend_name;​

Answer

Something like this should work:

SELECT vend_name
  FROM Vendor
 WHERE vend_id IN (
        SELECT vend_id
          FROM Product
         GROUP BY vend_id
        HAVING COUNT(DISTINCT product_id) = 1
       )
;

 
SELECT vend_name  FROM Vendor WHERE vend_id IN (        SELECT vend_id          FROM Product         GROUP BY vend_id        HAVING COUNT(DISTINCT product_id) = 1       );​

Advertisement

Answer