CODEWITHSUNDEEP
sqloracle-database
KeyGy
KeyGy

Reputation: 35

Select the producs of a provider with the lower product

i have two tables, products and provider

Provider:

nif
user

Products:

cod_prod
name
price
provider
stock

Expected Result:

for example we have in the table products this;

cod_prod name Price Provid Stock
1 ball 2.00 peter 20
2 shoe 1.00 peter 20
3 flower 3.00 peter 20
4 bag 7.00 peter 20
5 balloon 3.00 peter 20
6 gun 2.00 martin 20
7 shotgun 4.00 martin 20
8 cheese 7.00 martin 20
9 shirt 7.00 martin 20
10 sunglass 6.00 martin 20

in this scenario the product with the lower price is the shoe,

2 shoe 1.00 peter 40

What i want if that after the query get this result of the lower product price, i get this result:

   name  provider 
   ball   peter     
   shoe   peter     
 flower   peter     
    bag   peter     
balloon   peter

Because the query after selecting the lower product price, get all the products from the provider with the low product price.

i have this query at the moment

select provider.user, products.name from provider
inner join products
on provider.nif = products.provider
where products.price = (select min(products.price) from products);

With this i can select the most cheap item, but i do not know how to resolve, the thing is that i want a query that with the condition of the lowest cheap item i can get the list of products of that provider.

P.D: I updated the question with the expected result and examples of the product table to be more specific about what i want to obtain.

Upvotes: 0

Views: 112

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Here's one option; it ranks products per price and then uses that CTE (rank_them) to fetch data you're interested in.

SQL> with
  2  -- sample data
  3  provider (nif, cuser) as
  4    (select 1, 'Provider 1' from dual union all
  5     select 2, 'Provider 2' from dual
  6    ),
  7  products (cod_prod, name, price, provider) as
  8    (select 1, 'Sugar', 10, 1 from dual union all
  9     select 2, 'Milk' , 20, 1 from dual union all
 10     select 3, 'Salt' ,  5, 2 from dual union all   --> cheapest
 11     select 4, 'Water', 15, 2 from dual
 12    ),
 13  -- query begins here
 14  rank_them as
 15    (select p.*,
 16          rank() over (order by price) rnk
 17     from products p
 18    )
 19  select d.nif, d.cuser, r.cod_prod, r.name, r.price
 20  from rank_them r join provider d on d.nif = r.provider
 21  where r.provider = (select r1.provider
 22                      from rank_them r1
 23                      where r1.rnk = 1
 24                     );

       NIF CUSER        COD_PROD NAME       PRICE
---------- ---------- ---------- ----- ----------
         2 Provider 2          3 Salt           5
         2 Provider 2          4 Water         15

SQL>

Upvotes: 1

Related Questions