Only show categories if entries exist in that category AND in another separate category

Question

I need to create a megamenu for a site which links to a bunch of category pages.

So I have megamenus for Product Type A & B which are also categories.

Under Product Type A there are headings for Materials, Brand & Usage.

Product Type B will also have a megamenu with a Brand subheading.

At the moment I have a category group for Brands and I'm just listing out all the categories in that group under the respective headings.

So what I need to do is make sure that under the Brand heading for Product Type A that only Brands that have entries that are also assigned to the Product Type A category show, and so on for Type B etc.

For most of the options in these menus I'm just dealing with categories because there won't be any overlap.

But there will for Brands and so I need to look at the entries and then build the list of category options from there.

Any idea how I go about that?

Answer 1

This is what I ended up doing:

{exp:query sql="SELECT COUNT(entry_id) num_cat_entries
    FROM
    (
            SELECT  entry_id
            FROM    exp_category_posts
            WHERE   cat_id IN (31, 73)
            GROUP   BY entry_id
            HAVING  COUNT(DISTINCT cat_id ) = 2
    ) s"
}
{if num_cat_entries > 0} show the category link {/if}
{/exp:query}

Answer 2

What will really make your day is the GWcode Categories add-on. If you set the parameter show_empty="no" you're good to go. Documentation here.

Let us know if we can be of any more help!

EDIT: native EE also has this built in