CODEWITHSUNDEEP
mysql
Syed Mudabbir
Syed Mudabbir

Reputation: 287

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

Upvotes: 3

Views: 57

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

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.

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133380

use left join on oc_product_special

    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
        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 ASC

Inner join si for matching value in you case somethings there aren't match so ..use left join

Upvotes: 1

Related Questions