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.

Rahul · Accepted Answer · 2015-09-17 12:59:19Z

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;

answered Sep 17, 2015 at 12:59

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user4676307 Over a year ago

Thank you Rahul that does what I needed. Will you please be able to tell me how can I had another case as I would like to select data where the attribute.name has product type and form factor

Rahul Over a year ago

@user4676307, it can be like general syntax case when condirtion then value when con2 then val2 else val3 end

user4676307 Over a year ago

I get the case statement but will I have another left joint for attribute table ? LEFT JOIN attributes AS attributes_2 ON products.product_id = attributes_2.product_id

user4676307 Over a year ago

CASE WHEN attributes_2.name = 'Form Factor' THEN attributes_2.value ELSE NULL END AS Value

Rahul Over a year ago

@user4676307, No No, just use a IN operator in my answer like CASE WHEN IFNULL(attributes.name,'') IN ('Product Type' ,'From actor')

|

jeoj · Accepted Answer · 2015-09-17 12:51:57Z

0

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

answered Sep 17, 2015 at 12:51

jeoj

6536 silver badges9 bronze badges

1 Comment

user4676307 Over a year ago

I can not remove "attributes.name = 'Product Type'" from where statement as attributes tables is has loads of other name and I only need to select where attribute.name is product type

Collectives™ on Stack Overflow

MySql Select Statement Issue

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related