Выбор данных таблицы по условиям связанной таблицы sql

Question

Не могу получить записи по условию из связанной таблицы Имеются две таблицы account и связанная с ней order по полю account_id
account

order

Нужно получить записи account ТОЛЬКО Marina (id=3) по запросу WHERE pay > 500 and pay < 2000, т.к. только 900 и 600 проходять условие. т.е. если ВСЕ ORDER.PAY данного аккаунта соответствует условию но т.к. такие значения есть в других order у др. account, они тоже попадают в выборку. Нужно что бы все имеющиеся связанные данные соответствовали условию, если хоть одна строка не соответсвует условию, данный аккаунт не должен попасть в выборку
Используя WHERE и BETWEEN

SELECT 
  `account`.* , `order`.`pay`
FROM 
  `account` 
  LEFT JOIN `order` ON `account`.`id` = `order`.`account_id` 
WHERE 
  `order`.`pay` BETWEEN 500 
  AND 2000
GROUP BY 
  `account`.`id`

id  name    pay
1   Elena   1200
2   Olga    1200
3   Marina  900

Используя WHERE и <>

SELECT 
  `account`.* , `order`.`pay`
FROM 
  `account` 
  LEFT JOIN `order` ON `account`.`id` = `order`.`account_id` 
WHERE 
  `order`.`pay` > 500 
  AND `order`.`pay` < 2000
GROUP BY 
  `account`.`id`

Получаю так же

id  name    pay
1   Elena   1200
2   Olga    1200
3   Marina  900

Используя HAVING

SELECT 
  `account`.* , `order`.`pay`
FROM 
  `account` 
  LEFT JOIN `order` ON `account`.`id` = `order`.`account_id` 
GROUP BY 
  `account`.`id` 
HAVING 
  (
    SUM(order.pay) >= 500
  ) 
  AND (
    SUM(order.pay) <= 2000
  )

Получаю

id  name    pay
1   Elena   475
2   Olga    475
3   Marina  900

но нужно получить ACCOUNT только Marina, т.к. только у этой записи все ORDER.PAY соответсвуют условию pay от 500 до 2000

Буду признателен за помощь

Answer 1

Если навскидку для MySql, то можно вот так:

create table account (
  id int, 
  name varchar(50));

create table orders (
  id int,
  pay int,
  account_id int);  
insert into account values
(1, 'Elena'),
(2, 'Olga'),
(3, 'Marina');

insert into orders values
(1, 475,  1),
(2, 1200, 1),
(3, 475,  2),
(4, 1200, 2),
(5, 475,  2),
(6, 900,  3),
(7, 600,  3);

select * from account where id in 
    (select distinct TT.account_id from 
        (select account_id,
            max(pay) over (partition by account_id order by pay asc) as max_pay,
            min(pay) over (partition by account_id order by pay asc) as min_pay
            from orders) TT
        where TT.max_pay < 2000 and TT.min_pay > 500
    );

Answer 2

Получить записи по id, которые удовлетворяют всем условиям и не выводить записи по id, где есть хотя бы одна запись, не удовлетворяющая условию, можно, воспользовавшись оконными функциями.

Как решаем задачу:

Цепляем 2 таблицы по id=account_id
Считаем, сколько всего строк есть по каждому ID(Оконой функцией выводим количество строк COUNT, с группировкой по id PARTITION BY)
Считаем, сколько строк удовлетворяют заданному условию(Второй окнонной функцией считаем количество записей, удовлетворяющих заданному условию, для этого используем внутри count конструкцию case, группируя по ID)

Итого получаем вот такой SQL запрос:

select * from (
  select
      a.id
    , a.name
    , o.pay
    , o.id id_pay
    , count(a.id) over(partition by a.id) as all_record
    , count(case when o.pay between 500 and 2000 then 1 end) over(partition by a.id) as need_record
  from account a
  join orders o on a.id = o.account_id
) r
where r.all_record = r.need_record;

Answer 3

Можно прямо поставить Ваши условия в WHERE

для account_id все строки в orders, имеют pay>500 или pay<2000

что сформулируем так:

для account_id не существуют строки в orders, где pay<=500 или pay>=2000.

select *
from account a
where not exists (
    select pay 
    from orders o2 
    where o2.account_id=a.id 
     and (pay<=500 or pay>=2000) )

id	name
3	Marina

Или шире, в JOIN с orders

select *
from account a
left join orders o on o.account_id=a.id
where not exists(
    select pay 
    from orders o2 
    where o2.account_id=a.id and (pay<=500 or pay>=2000) )

id	name	id	pay	account_id
3	Marina	7	600	3
3	Marina	6	900	3

Answer 4

Решил дополнить ответ, мало ли кому пригодиться, ввиду того что связанные записи могут иметь нулевые значения, а так же у основной таблицы если нет связанных строк то они будут попадать в выборку. Для этого я дополнил запрос.

 create table accounts (
  id int, 
  name varchar(50)
);
create table orders (
  id int, total_paid int, account_id int
);
insert into accounts 
values 
  (1, 'Elena'), 
  (2, 'Olga'), 
  (4, 'Ego'), 
  (5, 'Mich'), 
  (6, 'Kol'), 
  (8, 'RRR'), 
  (9, 'Emty'), 
  (3, 'Marina');
insert into orders 
values 
  (1, 475, 1), 
  (2, 475, 1), 
  (3, 475, 2), 
  (4, 1200, 2), 
  (5, 475, 2), 
  (6, 800, 3), 
  (8, 900, 3), 
  (9, 475, 6), 
  (10, 900, 4), 
  (11, 900, 5), 
  (12, 900, 3), 
  (13, null, 3), 
  (18, 0, 3), 
  (20, null, 9), 
  (7, 600, 3);
SELECT 
  * 
FROM 
  accounts;
SELECT 
  * 
FROM 
  orders;
SELECT 
  `accounts`.* 
FROM 
  `accounts` 
  left join orders on orders.account_id = accounts.id 
WHERE 
  NOT EXISTS (
    SELECT 
      `account_id` 
    FROM 
      orders 
    WHERE 
      `orders`.`account_id` = `accounts`.`id` 
      AND (
        `orders`.`total_paid` >= 480 
        OR `orders`.`total_paid` <= 300
      )
  ) 
  AND orders.id IS NOT NULL 
  AND orders.total_paid IS NOT NULL

БЛОГ НА HUSL

Выбор данных таблицы по условиям связанной таблицы sql

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