SQL select min() using SQL join

I need result like this (in Oracle SQL):

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);

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;

Advertisement

Answer