CODEWITHSUNDEEP
phpmysqlsql
Sardar
Sardar

Reputation: 33

one product has multiple prices

i have been trying now 2 days and doing research about this but i din't find any solution for this

so i have 3 tables what i want is that if a product is having only single price then show that single price if the product is having multiple prices then show the min price and max price of that product.

so in product id number 2 is having 3 prices.

so if the product is having single price then each product will have their price if product has multiple prices then display min price $ 19.00 __ $ 26.00 max price.

so how can i do this?? how can i display it on the page???

product (id,  price)
         1     1
         2     2
         3     3
product_has_price (product_id, price_id)
                       1         1
                       2         2
                       2         3
                       3         4
                       2         5
price (id,  price)
        1   14.00
        2   19.00
        3   24.00
        4   35.00
        5   26.00

thanks if anybody helps

Upvotes: 0

Views: 747

Answers (1)

Andomar
Andomar

Reputation: 238078

select  p.id
,       case 
        when min(i.price) <> max(i.price) then 
            concat('$ ', min(i.price), '__ $ ', max(i.price))
        else concat('$ ', min(i.price))
        end as PriceDescription
from    product p
join    product_has_price php
on      p.id = php.product_id
join    price i
on      i.id = php.price_id
group by
        p.id

Upvotes: 1

Related Questions