Find a laboratory where elements are not repeated POSTGRESQL-CodePudding

I have multiple tables:

create table computer(
    id int primary key,
    id_monitor int references monitor(id),
    laboratory_number int not null
);

create table monitor(
    id int primary key,
    id_company int references company_monitor(id),
);

create table company_monitor(
    id int primary key,
    name varchar(10)
);

I want to get the number of a laboratory where all monitor manufacturers are different (don`t repeat). I imagined this implementation as follows: we look for how many times manufacturers repeat and find where they repeat 1 time. But the difficulty is that the total number of repetitions, and i need it in each individual laboratory.

Example: Table "computer"

 id | id_monitor | id_company_cpu |    cpu_name    | ram | ssd |           gpu           | laboratory_number
---- ------------ ---------------- ---------------- ----- ----- ------------------------- -------------------
  1 |          1 |              1 | Core i5 10800F |   8 | 500 | Nvidia GeForce GTX 1050 |                 1
  2 |          2 |              2 | Ryzen 5 3600   |   8 | 500 | Radeon RX 580           |                 2
  3 |          3 |              1 | Core i5 10800F |   8 | 500 | Nvidia GeForce GTX 1050 |                 1
  4 |          4 |              1 | Core i5 10800F |   8 | 500 | Nvidia GeForce GTX 1050 |                 1
  5 |          5 |              2 | Ryzen 5 3600   |   8 | 500 | Radeon RX 580           |                 2

Table "monitor"

 id | id_company | diagonal | id_matrix | display_resolution
---- ------------ ---------- ----------- --------------------
  1 |          3 |       24 |         2 | 1980*1280
  2 |          2 |       27 |         1 | 2560*1440
  3 |          3 |       24 |         3 | 1980*1280
  4 |          3 |       24 |         1 | 1980*1280
  5 |          2 |       24 |         3 | 1980*1280

Table "company_monitor"

 id |  name
---- ---------
  1 | ASUS
  2 | ACER
  3 | SAMSUNG

Query result should be like this (result: laboratory number 1, since all monitor manufacturers are different in it, laboratory number 2 has the same):

labalatory_number
-----------------
        1

I made this request:

select  monitor.id_company, count(*)
    from monitor join computer on computer.id_monitor = monitor.id 
        group by monitor.id_company

CodePudding user response：

check this out

with w as (
  select distinct c.laboratory_number, c.id_monitor, m.id_company
    from computer c join monitor m on c.id_monitor = m.id
)
select laboratory_number
  from w 
  group by laboratory_number
  having count(1) = count(distinct id_company)