Search code examples
sqlpostgresqlgroup-byhaving

Selecting fields that are not in GROUP BY when nested SELECTS aren't allowed

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

Solution

  • 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.