CODEWITHSUNDEEP
mysqlsqlrelational-database
Marcelo Assis
Marcelo Assis

Reputation: 5214

How can I make a query, selecting only CATEGORIES which have PRODUCTS into it?

I have a DB structured like this: 

CATEGORIES > SUBCATEGORIES > PRODUCTS

In just want show the categories which have an number of products related to it, but I don't know if my method is the best. I thinking in putting some SELECT statement inside the first WHERE, but this sounds "unpratical". I searched into Google/MySQl docs and nothing helped me.

Example of what I have done with CATEGORIES > SUBCATEGORIES:

SELECT c.*
FROM categories c
WHERE 
(
   SELECT count(*)
   FROM subcategories sc
   WHERE sc.id_categories = c.id
) > 2

With this query I can sucessfully see which categories have more than 2 subcategories related to them, I just could do the same, adding the PRODUCTS table to this query. But I almost sure this is going to slow the query. There is some more fast way of doing this type of query? Thanks!

Upvotes: 1

Views: 1232

Answers (2)

Magnus
Magnus

Reputation: 46997

Something like this, I don't know your FK relations, so I'm just guessing here.

SELECT *
FROM Categories
WHERE EXISTS(
  SELECT NULL
  FROM products
  JOIN SubCategories ON products.fkSubCatID = SubCategories.PkSubCatID
  WHERE SubCategories.fkCatID = Categories.pkCatID
  HAVING Count(*) > 2)

Upvotes: 2

Cem Kalyoncu
Cem Kalyoncu

Reputation: 14603

Sub selects are designed for this purpose.

SELECT * FROM categories 
WHERE 
   (  SELECT COUNT(id) 
      FROM subcategories 
      WHERE 
        category=categories.id AND 
        (  SELECT COUNT(id) 
           FROM products 
           WHERE 
             subcategory=subcategories.id
        )
    )

Note that this query might not be optimal. And might not work as is not tested.

EDIT:
Following probably will work faster:

SELECT * FROM categories 
WHERE 
   EXISTS(
      SELECT COUNT(id) 
      FROM subcategories 
      WHERE 
        category=categories.id AND 
        EXISTS(
           SELECT *
           FROM products 
           WHERE 
             subcategory=subcategories.id
        )
    )

Upvotes: 2

Related Questions