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”
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;
Advertisement
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
)
;