Syed Mudabbir Syed Mudabbir - 3 months ago 9
MySQL Question

Missing Data in mysql query, Need conditional statement in inner join

I have been having issues with missing data when I run the following query.
There are some products which has special price, stored in table

oc_product_special
and some products has regular price stored in table
oc_product
.

I figured out that it is only showing data if there is a special price in table
oc_product_special
. it is omitting any data where there is no special price and only regular price. I am not sure how to fix this problem. How and where i can add conditional statement or something like


if there is a regular price present and no special price then show regular price and 0 for special price.




SELECT
pd.name AS 'Product Name',
p.model AS UPC,
p.quantity AS 'Quantity',
p.price AS 'Regular Price',
ps.price AS 'Special Price',
p.cost AS 'COST',
p.status AS 'Status'
FROM oc_product p
INNER JOIN oc_product_description pd
ON pd.product_id = p.product_id
INNER JOIN oc_product_special ps
ON ps.product_id = p.product_id
INNER JOIN oc_manufacturer m
ON p.manufacturer_id = m.manufacturer_id
INNER JOIN oc_product_to_category p2c
ON p2c.product_id = p.product_id
INNER JOIN oc_category c
ON c.category_id = p2c.category_id
INNER JOIN oc_category_description cd
ON c.category_id = cd.category_id
WHERE
c.category_id = 37 OR c.parent_id = 37
GROUP BY pd.name ORDER BY m.name ASC



Answer

Use LEFT JOIN, which will retain records on the left side of the join even if they do not match to anything on the right side:

SELECT COALESCE(pd.name, 'NA') AS 'Product Name', 
       p.model AS UPC,  
       p.quantity AS 'Quantity', 
       p.price AS 'Regular Price', 
       COALESCE(ps.price, 0.0) AS 'Special Price', 
       p.cost AS 'COST', 
       p.status AS 'Status'
FROM oc_product p
LEFT JOIN oc_product_description pd
    ON pd.product_id = p.product_id
LEFT JOIN oc_product_special ps
    ON ps.product_id = p.product_id
INNER JOIN oc_manufacturer m
    ON p.manufacturer_id = m.manufacturer_id
INNER JOIN oc_product_to_category p2c 
    ON p2c.product_id = p.product_id
INNER JOIN oc_category c 
    ON c.category_id = p2c.category_id
INNER JOIN oc_category_description cd 
    ON c.category_id = cd.category_id
WHERE c.category_id = 37 OR
      c.parent_id = 37
GROUP BY pd.name
ORDER BY m.name

Explanation:

In a LEFT JOIN, when a record on the left side of the join does not match to anything on the right side, the columns from the right side will all appear as NULL in the result set. I used the COALESCE function in my query, which will conditionally replace the first argument with the second if the former be NULL. In this case, the special price will be replaced with zero if NULL. I also used it with the product name in case names be missing in some cases.

Comments