Search code examples
mysqlsubquery

MySQL query base on availibility of in another table

I'm brand new in MySQL and I need some help how to build simple query. I need to get some result selecting "filtered" data from one table linked with another table based on conditions inside this second table.

In the first table named "categories" with following data inside:

CategoryID CategoryName
1 food
2 drinks
3 sweets

In the second table named "product" there are following records:

product CategoryID Quantity
apple 1 5
banana 1 0
vodka 2 0
beer 2 10
chocolate 3 0
biscuits 3 0

Both tables are linked by the column CategoryID.

I would like to build a query to return as results only the categories / CategoryName/, where there are products under this category with Quantity>0.

Expected result is:

CategoryName
food
drinks
Solution

  • This should do it:

    SELECT
    DISTINCT categories.CategoryName
FROM
    categories
JOIN product ON product.CategoryID = categories.CategoryID
WHERE
    product.Quantity > 0;