How to write/append a complex SQL query?

Question

PostgreSQL

Hello, I have 3 tables:
number:
- id
- country

buy_number:
- id
- numberId
- serviceId

service:
- id
- name

I need to write a query with which I will get a list of non-services (service.name) and countries (number. country). But only those services that are not yet sold (are not in the buy_number table) need to be received.

I could only write like this, I get a list of countries for the numbers of which there are services not yet sold:

SELECT
       pn.country
FROM number pn
    LEFT JOIN buy_number bn on pn.id = bn."numberId"
    LEFT JOIN service s on s.id = bn."serviceId"
GROUP BY pn.country
HAVING COUNT(DISTINCT bn."serviceId") < (SELECT COUNT(id) from service)

I check whether there are services for the number that have not yet been sold or not, I do according to the condition in HAVING, if the number of sold services for the number is less than the total number of services in the service table, then this number still has free services.

It is necessary to somehow add this request so that I would receive not only the countries from the numbers where there are services that are not sold, but also the names of these services that are not sold.

Thank you!

Answer 1

Using CROSS JOIN

SELECT
  pn.country, array_agg(s.name) services
FROM number pn
CROSS JOIN service s
LEFT JOIN buy_number bn ON bn.numberId = pn.id AND bn.serviceId = s.id
WHERE bn.id is NULL
GROUP BY pn.country;

Check SQL