sql - 将 6 个表加入单个查询？

Question

嘿，谁能帮我将下面的 5 个表合并到一个查询中？我目前有下面的查询，但似乎不起作用，就好像在招聘表中有两个具有相同 ID 的产品一样，所有产品都是从产品表中返回的，这显然是错误的。

SELECT products.prod_id, products.title, products.price, product_types.name,  
listagg(suppliers.name, ',') WITHIN GROUP(ORDER BY suppliers.name) suppliers
FROM products 
INNER JOIN product_suppliers ON products.prod_id = product_suppluer.prod_id 
INNER JOIN product_types ON product_types.type_id = products.type_id 
INNER JOIN suppliers ON product_suppliers.supp_id = suppliers.supp_id 
LEFT OUTER JOIN hires ON hires.prod_id = products.prod_id 
    WHERE (hires.hire_end < to_date('21-JAN-13') OR hires.hire_start > to_date('26-JAN-13')) 
    OR hires.prod_id IS NULL 
GROUP BY products.prod_id, products.title, products.price, product_types.name

表数据:

PRODUCTS                 
--------------------------------------------
| Prod_ID  | Title   | Price   | Type_ID   |
|------------------------------------------|
| 1        | A       | 5       | 1         |
| 2        | B       | 7       | 1         |
| 3        | C       | 3       | 2         |
| 4        | D       | 3       | 3         |
|------------------------------------------|

PRODUCT_TYPES                 
----------------------
| Type_ID  | Type    |
|--------------------|
| 1        | TYPE_A  |
| 2        | TYPE_B  |
| 3        | TYPE_C  |
| 4        | TYPE_D  |
|--------------------|

PRODUCT_SUPPLIERS                 
-------------------------
| Prod_ID  | Supp_ID    |
|-----------------------|
| 1        | 1          |
| 1        | 2          |
| 2        | 2          |
| 3        | 3          |
| 4        | 4          |
|-----------------------|

SUPPLIERS                 
----------------------
| Supp_ID  | Name    |
|--------------------|
| 1        | SUPP_A  |
| 2        | SUPP_B  |
| 3        | SUPP_C  |
| 4        | SUPP_D  |
|--------------------|

HIRES                
---------------------------------------------------------------
| Hire_ID  | Prod_ID    | Cust_ID    | Hire_Start | Hire_End  |
|-----------------------|------------|------------------------|
| 1        | 1          | 1          | 22-Jan-13  | 23-Jan-13 |
| 2        | 2          | 2          | 27-Jan-13  | 29-Jan-13 |
| 3        | 1          | 3          | 30-Jan-13  | 31-Jan-13 |
|-----------------------|------------|------------|-----------|

PRODUCTS                 
--------------------------------
| Cust_ID  | Name    | Phone   |
|------------------------------|
| 1        | Cust_A  | 555-666 |
| 2        | Cust_B  | 444-234 |
| 3        | Cust_C  | 319-234 |
| 4        | Cust_D  | 398-092 |
|------------------------------|

目前查询的输出如下所示:

-------------------------------------------------------------
| Prod_ID  | Title   | Price   | Type_ID   | Suppliers      |
|------------------------------------------|----------------|
| 1        | A       | 5       | Type_A    | SUPP_A,SUPP_B |
| 2        | B       | 7       | Type_B    | SUPP_B         |
| 3        | C       | 3       | Type_C    | SUPP_C         |
| 4        | D       | 3       | Type_D    | SUPP_D         |
|------------------------------------------|----------------|

什么时候应该看起来像这样？因为 Prod_ID '1' 在查询中的日期之间被租用

-------------------------------------------------------------
| Prod_ID  | Title   | Price   | Type_ID   | Suppliers      |
|------------------------------------------|----------------|
| 2        | B       | 7       | Type_B    | SUPP_B         |
| 3        | C       | 3       | Type_C    | SUPP_C         |
| 4        | D       | 3       | Type_D    | SUPP_D         |
|------------------------------------------|----------------|

如果有人可以帮助将查询修改为建议的输出，我将不胜感激。因为我的理解是它应该按书面方式工作？

Answer 1

您的问题是 Prod_Id 1 在这些日期范围内和外。因此，请改用子查询来过滤掉哪些 Prod_Id 在这些范围内，并排除它们。

这是查询的简化版本:

SELECT P.Prod_ID
FROM Products P 
 LEFT JOIN (
   SELECT Prod_ID 
   FROM Hires
   WHERE hire_end >= To_Date('20130121', 'yyyymmdd') AND hire_start <= To_Date('20130126', 'yyyymmdd')
   ) H ON P.Prod_ID = H.Prod_ID
WHERE h.prod_id IS NULL

和 SQL Fiddle .

假设我正确复制和粘贴，这应该是您的查询:

SELECT products.prod_id, products.title, products.price, product_types.name,  
listagg(suppliers.name, ',') WITHIN GROUP(ORDER BY suppliers.name) suppliers
FROM products 
INNER JOIN product_suppliers ON products.prod_id = product_suppluer.prod_id 
INNER JOIN product_types ON product_types.type_id = products.type_id 
INNER JOIN suppliers ON product_suppliers.supp_id = suppliers.supp_id 
LEFT JOIN (
   SELECT Prod_ID 
   FROM Hires
   WHERE hire_end >= To_Date('20130121', 'yyyymmdd') AND hire_start <= To_Date('20130126', 'yyyymmdd')
   ) H ON products.Prod_ID = H.Prod_ID
WHERE H.Prod_ID IS NULL
GROUP BY products.prod_id, products.title, products.price, product_types.name

希望这可以帮助。