I need result like this (in Oracle SQL):
x
Name Producer Min Price
HDD 250 gb Western Digital 6000
HDD 500 gb Corsair 4000
HDD 750 gb Corsair 6300
and I can get it with this SQL
select p.name, pr.name, c.price
from product p, producer pr, catalog c
where p.product_id = c.pp_product_id and pr.producer_id = c.pp_producer_id
and (p.product_id, c.price) in (select p.product_id, min (c.price) from product p, catalog c where p.product_id = c.pp_product_id group by p.product_id);
or with this:
select p.name, pr.name, c2.minprice
from product p, producer pr, catalog c, (select pp_product_id, min(price) as minprice from catalog c1 group by pp_product_id) c2
where p.product_id=c.pp_product_id and pr.producer_id=c.pp_producer_id
and p.product_id=c2.pp_product_id and c.price=c2.minprice;
But I am trying to get that result using SQL JOINS, like this:
select a.name, b.name, min(c.price)
from catalog c inner join product a on c.pp_product_id=a.product_id
inner join producer b on c.pp_producer_id = b.producer_id
group by a.name, b.name;
But it doesn’t produce desired result, it outputs all products and producers prices. Any help with this?
DDL is:
create table product (
product_id number primary key,
name varchar(255)
);
create table producer (
producer_id number primary key,
name varchar(255)
);
create table catalog (
pp_product_id number,
pp_producer_id number,
price number
);
alter table catalog add constraint pp_product_id1 foreign key (pp_product_id) references product (product_id);
alter table catalog add constraint pp_product_id2 foreign key (pp_producer_id) references producer (producer_id);
insert into product (product_id, name) values (1, 'HDD 250 gb');
insert into product (product_id, name) values (2, 'HDD 500 gb');
insert into product (product_id, name) values (3, 'HDD 750 gb');
insert into producer (producer_id, name) values (1, 'Hitachi');
insert into producer (producer_id, name) values (2, 'Corsair');
insert into producer (producer_id, name) values (3, 'Western Digital');
insert into catalog (pp_product_id, pp_producer_id, price) values (1,1, 8000);
insert into catalog (pp_product_id, pp_producer_id, price) values (1,3, 6000);
insert into catalog (pp_product_id, pp_producer_id, price) values (2,1, 7500);
insert into catalog (pp_product_id, pp_producer_id, price) values (2,2, 4000);
insert into catalog (pp_product_id, pp_producer_id, price) values (3,2, 6300);
insert into catalog (pp_product_id, pp_producer_id, price) values (3,3, 10000);
Advertisement
Answer
The issue isn’t the join
. It is the selection of the best row. To get the best row use row_number()
(chooses arbitrarily if the “best” is duplicated) or rank()
(choose all best when there are duplicates).
select product, producer, price
from (select a.name as product, b.name as producer, c.price,
row_number() over (partition by a.name order by c.price asc) as seqnum
from catalog c inner join
product a
on c.pp_product_id = a.product_id inner join
producer b
on c.pp_producer_id = b.producer_id
) cpp
where seqnum = 1;