Как сравнить несколько значений в sql?

Question

У меня есть таблицы Организации, Сотрудники и Заказы. Например 3 организации, у каждой из которых есть несколько сотрудников с разными зарплатами и несколько заказов разной стоимости. Как мне выбрать организации, в которых сумма заказов меньше суммы зарплат?

CREATE TABLE Organization
(
    Id INT,
    House_Id INT,
    NameOfOrganization NVARCHAR(30),
    CONSTRAINT PK_Organization_Id PRIMARY KEY (Id),
    CONSTRAINT FK_Organization_To_House FOREIGN KEY (House_Id) REFERENCES House (Id) ON DELETE SET NULL
)

CREATE TABLE Employees
(
    Id INT,
    Organization_Id INT,
    FirstName NVARCHAR(20) NOT NULL,
    LastName NVARCHAR(20) NOT NULL,
    BirtDate DATE,
    Post NVARCHAR(20) NOT NULL,
    Sallary MONEY,
    NameOfOrganization NVARCHAR(30),
    CONSTRAINT PK_Employees_Id PRIMARY KEY (Id),
    CONSTRAINT FK_Employees_To_Organization FOREIGN KEY (Organization_Id) REFERENCES Organization (Id) ON DELETE SET NULL
)

CREATE TABLE Orders
(
    Id INT,
    Organization_Id INT,
    Cost MONEY,
    OrderDescription NVARCHAR(30),
    CONSTRAINT PK_Orders_Id PRIMARY KEY (Id),
    CONSTRAINT FK_Orders_To_Organization FOREIGN KEY (Organization_Id) REFERENCES Organization (Id) ON DELETE SET NULL
)

Мой запрос:

SELECT * 
    FROM Organization 
    WHERE Id IN (SELECT Organization_Id FROM Employees WHERE
                (SELECT SUM(Cost) FROM Orders GROUP BY Organization_Id) 
                <
                (SELECT SUM(Sallary) FROM Employees GROUP BY Organization_Id))

После него я получаю ошибку:

 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.`

Answer 1

Как-то так:

SELECT * 
FROM Organization o
WHERE EXISTS (
  SELECT 1
  FROM (
    SELECT Organization_Id, SUM(Cost) AS cost
    FROM Orders
    GROUP BY Organization_Id) AS c
  JOIN (
    SELECT Organization_Id, SUM(Sallary) AS salary
    FROM Employees
    GROUP BY Organization_Id) AS s
  ON (c.Organization_id = s.Organization_id)
WHERE c.cost < s.salary
  AND c.Organization_id = o.id
)

БЛОГ НА HUSL

Как сравнить несколько значений в sql?

Ответы (1 шт):