Question

Views

I have the tables:

Product(code (PK), pname, (....), sid (FK)),
Supplier(sid(PK), sname, (....))

The assignment is:

Find Suppliers that supply only one product. Display their name (sname) and product name (pname).

It seem to me like a GROUP BY problem, so I used:

SELECT sid FROM
Product GROUP BY sid
HAVING CAST(COUNT(*) AS INTEGER) = 1;

This query have found me the list of sid's that supply one product only, but now I have encountered a problem:

The assignment forbids any form of nested SELECT queries.
The result of the query I have written has only one column. (The sid column)
Thus, I am unable to access the product name as it is not in the query result table, and if I would have added it to the GROUP BY statement, then the grouping will based on product name as well, which is an unwanted behavior.

How should I approach the problem, then?

Note: I use PostgreSQL

over 4 years ago · Santiago Trujillo

You could put

max(pname)

in the SELECT list. That's an aggregate, so it would be fine.

over 4 years ago · Santiago Trujillo Report

Answer question

Accepted Answer · 2022-05-11T20:45:16.782Z

You can phrase the query as:

SELECT s.sid, s.sname, MAX(p.pname) as pname
FROM Product p JOIN
     Supplier s
     ON p.sid = s.sid
GROUP BY s.sid, s.sname
HAVING COUNT(*) = 1;

You don't need to convert COUNT(*) to an integer. It is already an integer.