SQLize Online / PHPize Online  /  SQLtest Online

Create table Transactions (ID int not null, D_DATE date, CHN_ID int not null, AMOUNT int not null, MERCH Character(20), CITY Character(20)); Insert into Transactions (ID, D_DATE, CHN_ID, AMOUNT, MERCH, CITY) Values (101, to_date('01.12.2019','DD.MM.YYYY'), 1, 100, 'Merch1', 'Moscow'), (102, to_date('04.11.2019','DD.MM.YYYY'), 2, 500, 'Merch2', 'MOSCOW'), (103, to_date('12.12.2019','DD.MM.YYYY'), 4, 700, 'Merch1', 'Samara'), (104, to_date('01.12.2019','DD.MM.YYYY'), 3, 200, 'Merch3', 'Moscow'), (105, to_date('18.12.2019','DD.MM.YYYY'), 3, 300, 'Merch3', 'SamAra'), (106, to_date('20.12.2019','DD.MM.YYYY'), 4, 500, 'Merch2', 'Volgograd'), (107, to_date('04.12.2019','DD.MM.YYYY'), 4, 400, 'Merch4', 'Moscow'), (108, to_date('07.12.2019','DD.MM.YYYY'), 4, 400, 'Merch5', 'MoScow'); Create table Merch_tariff (MERCH Character(20), CHN_ID int, TARIFF Character(20)); Insert into Merch_tariff (MERCH, CHN_ID, TARIFF) Values ('Merch1', 1, '1,2 мин. 20 руб.'), ('Merch1', NULL, '1%'), ('Merch2', NULL, '2%'), ('Merch3', 3, '1,4% мин. 12 руб.'), ('Merch4', 4, '2% макс. 50 руб.'); Select t.*, coalesce(m1.tariff, m2.tariff, 'Не найден') applied_tariff From transactions t Left outer join merch_tariff m1 On m1.merch=t.merch and m1.chn_id=t.chn_id Left outer join merch_tariff m2 On m2.merch=t.merch and m2.chn_id is null;