MySql Select Statement Issue

Question

I am not sure what I am trying to achieve here is called which is why I will try to explain with pictures.

Below is the query that I have got:

SELECT DISTINCT
unspsc_commodity.`name` AS Commodity,
unspsc_code.`code` AS `Code`,
attributes.`value`
FROM
products
INNER JOIN unspsc_code ON products.unspsc_code_id =     unspsc_code.unspsc_code_id
INNER JOIN unspsc_commodity ON unspsc_code.unspsc_commodity_id =   unspsc_commodity.unspsc_commodity_id
INNER JOIN attributes ON products.product_id = attributes.product_id
WHERE attributes.`name` = 'Product Type'

Above query only select unspsc_commodity, unspsc_code and value where attribute.name has 'Product Type'. I would like to select unspsc_commodity, unspsc_code for products where there is no Product Type too. So where there is no Product Type in attribute table the query needs to select just the code and commodity and just output null for value. Is it possible to achieve.

Answer 1

Change your query to have LEFT JOIN like below and move the WHERE condition in select clause with a CASE expression

SELECT DISTINCT
unspsc_commodity.`name` AS Commodity,
unspsc_code.`code` AS `Code`,
CASE WHEN IFNULL(attributes.`name`,'') = 'Product Type' THEN attributes.`value` ELSE NULL END AS Value
FROM
products
INNER JOIN unspsc_code 
ON products.unspsc_code_id = unspsc_code.unspsc_code_id
INNER JOIN unspsc_commodity 
ON unspsc_code.unspsc_commodity_id = unspsc_commodity.unspsc_commodity_id
LEFT JOIN attributes 
ON products.product_id = attributes.product_id;

Answer 2

Try:

SELECT DISTINCT
unspsc_commodity.`name` AS Commodity,
unspsc_code.`code` AS `Code`,
attributes.`value`
FROM
products
INNER JOIN unspsc_code ON products.unspsc_code_id =     unspsc_code.unspsc_code_id
INNER JOIN unspsc_commodity ON unspsc_code.unspsc_commodity_id =   unspsc_commodity.unspsc_commodity_id
INNER JOIN attributes ON products.product_id = attributes.product_id
WHERE 1