mysql - 如何根据大小写更改查询

Question

我有一个具有以下架构的 MySql 表。

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"

用户正在传递一个 category_id，我正在使用该类别获取所有项目

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' ";

我想修改此查询，如果 product_imageurl 在 table_product_varients 中可用，则获取该图像，否则从 table_products 中获取 product_image_path 图像。目前查询正在从 table_products 中获取 product_image_path 图片。

有人可以帮助我吗。

我们将不胜感激任何帮助。

Answer 1

你可以避免这种情况:

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'