I have 3 tables. The first table is: packs_global_cats There are 3 global categories in it. Each of the global categories has its own subcategories. The table with subcategories is called: packs_cats . Here is its structure:

id global_cat name

The third table is the packages themselves. Here is their structure:

id cat name

Question.

How can I count the number of subcategories for a specific global category in which there are packages? The beginning of the query I have is:

 "SELECT COUNT(*) FROM packs_cats WHERE global_cat = '$global_cats[id]'"

This query will simply calculate how many subcategories there are for one global, but this query will not go into the table with packages and will not check whether there are packages in these subcategories.

It is necessary as with the help of LEFT JOIN, but unfortunately I do not understand how. help me please

    1 answer 1

    Option offhand (perhaps not the most optimal in speed):

     SELECT COUNT(DISTINCT c.id) FROM packs_cats AS c JOIN packs AS p ON c.id = p.cat WHERE c.global_cat = <global_cat_id>

    - here instead of <global_cat_id> you need to substitute the ID of the global category.

    PS: JOIN needed, not LEFT JOIN , because LEFT JOIN will select all categories - even those in which there are no packages.

    • Thank you very much! came up - WhoIsDT
    • You are welcome. Learn JOINs :) - rugabarbo