Haven't been able to crack this one for longer than I care to admit.
I don't reckon this could be that unusual a problem - surely mapping table troubles come a dime a dozen in SQL land - so I'll just ask, in the hope that others may benefit from my ignorance.
I'm trying pull together five tables to display the products that are connected to both a main category and a sub category. What I want to display is the information in the product table.
The tables are as follows:
**products**
product_id (PK) | [other columns with product information]
**categories**
category_id (PK) | category_name
**sub_categories**
sub_category_id (PK) | sub_category_name
**products_and_categories**
pc_rel_id (PK) | pc_product_id (FK - products.product_id) | pc_category_id (FK - categories.category_id)
**products_and_sub_categories**
psc_rel_id (PK) | psc_product_id (FK - products.product_id) | psc_sub_category_id (FK - sub_categories.sub_category_id)
My fruitless attempts so far include:
$getSCatProds = new QueryClass();
$catSubArray['queryCategory'] = 'main category name';
$catSubArray['querySubCategory'] = 'sub category name';
//PDO fetchAll(PDO::FETCH_ASSOC)
$resultArray = $getSCatProds->fetchAllQuery("SELECT product_id FROM products
INNER JOIN (products_and_categories, products_and_sub_categories)
ON (products_and_categories.pc_product_id=products.product_id
AND products_and_sub_categories.psc_product_id=products.product_id)
INNER JOIN (categories, sub_categories)
ON (categories.category_id = products_and_categories.pc_category_id
AND sub_categories.sub_category_id = products_and_sub_categories.psc_sub_category_id)
WHERE categories.category_name = :queryCategory
AND sub_categories.sub_category_name = :querySubCategory", $catSubArray);
print_r($resultArray); //returns an empty array
and:
//Query method and sent information same as in the above query
$resultArray = $getSCatProds->fetchAllQuery("SELECT product_id FROM products
INNER JOIN (products_and_categories, products_and_sub_categories, categories, sub_categories)
ON (products_and_categories.pc_product_id=products.product_id
AND products_and_sub_categories.psc_product_id=products.product_id)
WHERE categories.category_name = :queryCategory
AND sub_categories.sub_category_name = :querySubCategory", $catSubArray);
print_r($resultArray);
//returns a stupendous number of product_id:s, 2030 of them to be exact.
//There are 2075 items in the products table ...
Please, give my poor head a hand with finding the right query.
Thank you.