Magento Database – Products and Categories Coupling Table

There is no core functionality to archive this in the backend.

If you have database read access to run raw queries you can use the following queries to get an overview of all the categories that are linked to a certain product:

Option 1 => Get all categories of a product by entering the product's entity ID:

SELECT c3.sku, c2.value
FROM catalog_category_product c1
INNER JOIN catalog_category_entity_varchar c2 ON (c1.category_id = c2.entity_id)
INNER JOIN catalog_product_entity c3 ON (c1.product_id = c3.entity_id)
WHERE c2.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 3)
AND c3.entity_id = FILL_IN_PRODUCT_ID_HERE

Option 2 => Get all categories of a product by entering the product's SKU:

SELECT c3.sku, c2.value
FROM catalog_category_product c1
INNER JOIN catalog_category_entity_varchar c2 ON (c1.category_id = c2.entity_id)
INNER JOIN catalog_product_entity c3 ON (c1.product_id = c3.entity_id)
WHERE c2.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 3)
AND c3.sku = FILL_IN_PRODUCT_SKU_HERE

Ensure the followings as well.

• Your category is under the relevant root category.
• Under the Display Settings tab of category, the Display Mode should set to "Products Only" or "Static blocks and products". (If this is set to "Static block only`", the products will not display for the category.)
• Clear all the cache (including browser cache)
• Ensure all the products (including associate products) have qty > 0
• Make sure product inventory is set to 'Is in stock' => true
• Try this with Magento default theme, If the products are shown in the default theme, then the issue is with your custom theme.