CODEWITHSUNDEEP
mysql
Devesh Agrawal
Devesh Agrawal

Reputation: 9212

How to change the query based on case

I have a MySql Tables with the following schema. 

table_products - "product_id", product_name, product_description, product_image_path, brand_id
table_product_varient - "product_id", "varient_id", product_mrp, product_sellprice, product_imageurl
table_varients - "varient_id", varient_name
table_product_categories - "product_id", "category_id"

User is passing a category_id, and i am fetching all the items for that category using

my $sql_query = "SELECT
P.product_id, P.product_name,  V.varient_name, PV.product_mrp, PV.product_sellprice, P.product_image_path
FROM
table_products as P
INNER JOIN
table_product_categories as PC 
ON
  P.product_id = PC.product_id
INNER JOIN
table_product_varients as PV
ON
P.product_id = PV.product_id
INNER JOIN
table_varients as V
ON
V.varient_id = PV.varient_id
where 
PC.category_id = '$cate_id' ";

I want to modify this query such that, if product_imageurl is available in table_product_varients, fetch that image else fetch product_image_path image from table_products. At present query is fetching product_image_path image from table_products.

Can Please someone help me in this.

Any help will be highly appreciated.

Upvotes: 0

Views: 33

Answers (2)

Vijay
Vijay

Reputation: 1034

Replace this 

P.product_image_path

with

CASE 
  WHEN PV.product_imageurl IS NULL OR PV.product_imageurl = '' then
       P.product_image_path
ELSE 
     PV.product_imageurl
END

Upvotes: 1

planestepper
planestepper

Reputation: 3317

You can avoid the CASE:

SELECT
    P.product_id, 
    P.product_name, 
    V.varient_name, 
    PV.product_mrp, 
    PV.product_sellprice, 
    IFNULL(PV.product_imageurl, P.product_image_path) AS product_imageurl
FROM
    table_product_categories PC,
    table_varients V, 
    table_products P
        LEFT JOIN table_product_varients PV ON P.product_id = PV.product_id
where 
    P.product_id = PC.product_id
    AND V.varient_id = PV.varient_id
    AND PC.category_id = '$cate_id'

Upvotes: 2

Related Questions