how to union 2 tables but select values based on condition?

Question

there are 2 tables - book, supply. the supply table:

supply_id	title	author	price	amount
1	title1	author1	518.99	2
2	title2	author2	570.20	6
3	title3	author3	540.50	7
4	title4	author4	360.80	3

the book table:

book_id	title	author	price	amount
1	Мастер и Маргарита	Булгаков М.А.	670.99	3
2	Белая гвардия	Булгаков М.А.	540.50	5
3	Идиот	Достоевский Ф.М.	460.00	10
4	Братья Карамазовы	Достоевский Ф.М.	799.01	3
5	Игрок	Достоевский Ф.М.	480.50	10
6	Стихотворения и поэмы	Есенин С.А.	650.00	15

I need to union them but only select those rows that have higher price.

select title, price, amount 
from supply  a1
union all
select title, price, amount 
from book a2
where a1.price > a2.price

In case of items that exist in both tables then take the one with the highest price, is that what you want? — Giorgos Betsos, Feb 16 '21 at 15:56

score 1 · Answer 1 · answered Feb 16 '21 at 15:57

1

You can use window functions:

select bs.*
from (select bs.*,
             row_number() over (partition by title order by price desc) as seqnum
      from ((select supply_id, title, author, price, amount
             from book
            ) union all
            (select supply_id, title, author, price, amount
             from supply
            )
           ) bs
     ) bs
where seqnum = 1;

answered Feb 16 '21 at 15:57

Gordon Linoff

1,242,037
58
646
786

hmmm thx u, but this seems to be complicated, is there way to use where clause? – ERJAN Feb 16 '21 at 16:01

score 1 · Accepted Answer · answered Feb 16 '21 at 16:14

Try this:

select title, price, amount 
from supply s
where not exists (select 1 from book b where s.title = b.title and b.price > s.price)

union all

select title, price, amount 
from book b
where not exists (select 1 from supply s where s.title = b.title and s.price > b.price)

how to union 2 tables but select values based on condition?

2 Answers2