php - MySQL加入多个映射表

Question

无法破解这个问题的时间比我承认的要长。

我不认为这可能是一个不寻常的问题 - 当然 mapping table troubles在 SQL 领域一毛钱一打 - 所以我只问一下，希望其他人可以从我的无知中受益。

我正在尝试将五个表格放在一起以显示与主类别和子类别相关的产品。我要显示的是产品表中的信息。

表格如下:

**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)

到目前为止我没有结果的尝试包括:

$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

和:

//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 ...

请帮助我可怜的脑袋找到正确的查询。

谢谢。

Answer 1

您需要对每个表进行单独的连接。

SELECT p.product_id
FROM products AS p
JOIN products_and_categories AS pc ON p.product_id = pc.pc_product_id
JOIN categories AS c ON pc.pc_category_id = c.category_id
JOIN products_and_subcategories AS psc ON p.product_id = psc.psc_product_id
JOIN sub_categories AS sc ON psc.psc_sub_category_id = sc.sub_category_id
WHERE c.category_name = :queryCategory
AND sc.sub_category_name = :querySubCategory