MySQL - IF in a SELECT for multiple row column values

Question

I have a table named products that holds all the products e.g T-Shirt and product_variants that have the associated variants of the colors, eg. Black, Blue, Green which each has a stock column - that holds the number of stocks left.

I would like to build a query that can tell me which of the products that are totally sold out, and which are not.

The stock column should be 0 to be sold out, but if its NULL or > 0, it's in stock. NULL is used when we have unlimited quantities.

This is the following result i would like to get:

product_id|soldOut|stock
------------------
1         |Yes    |0
2         |Yes    |0
3         |No     |NULL
4         |No     |1

What i tried to do, is the following:

Grab all products
Make a join to product_variants
Group by product_id to only show one result per product.
SELECTing product_id and IF(stock > 0 OR stock IS NULL, 'No', 'Yes') as SoldOUt

Query:

SELECT p.product_id, IF(pv.stock > 0 OR pv.stock IS NULL, 'No', 'Yes') as SoldOUt
FROM products p
JOIN product_variants pv ON (`pv`.`product_id` = `p`.`product_id`)
GROUP BY p.product_id

The results is that soldOut is not correct - it seems like it just takes a random row of the variants and check the stock of this and answers.

Instead, It should check all of the variants row and see if any of them is not yet soldOut (with the if statement above) and then return one final answer for that product.

What have I done wrong and how can this be accomplished?

Gordon Linoff · Accepted Answer · 2016-04-30 13:14:56Z

1

First, you don't need a join, just aggregation.

So, here is one method that checks that all variants are 0:

select pv.product_id,
       (case when sum(pv.stock = 0) = count(*) then 'Yes' else 'No'
        end) as SoldOut
from product_variants pv 
group by p.product_id;

I am guessing that you also want sum(pv.stock) as well for the final column.

answered Apr 30, 2016 at 13:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Karem Over a year ago

Thank you! Gave me a new way to think.

Collectives™ on Stack Overflow

MySQL - IF in a SELECT for multiple row column values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related