mysql - Query returns a field which holds no data -

i have query comparisons across 2 tables , returns minimum out of results.

the problem is, i'm using inner joins , if there no matching row aka row not exist, field blank.

i'm wondering if theres way alter if row not exist it'll still return value.

this query:

select min(quantity_per_one * 5 < quantity) has_enough costs inner join user_ingredients using (ingredientid) 

if user has no ingredient row not exist, causes has_enough equal nothing. how can adjust it'll 0

table structure:

table costs

//note there can multiple ingredients per materialid id | materialid | ingredientid | quantity_per_one  1 |     5      |     6        |       60  1 |     5      |     3        |       10 

---

table user's available ingredients

id | userid | ingredientid | quantity 1 |    2    |     6        |    100 

you use left join , coalesce():

select coalesce(min(quantity_per_one * 5 <= quantity),0) has_enough costs left join user_ingredients using (ingredientid) 

update:

you need case statement you're after:

select min(case when quantity null 0                                else quantity_per_one * 5 < quantity                           end) 'has_enough' costs c left join user_ingredients ui   on c.ingredientid = ui.ingredientid 

demo: sql fiddle