Combining 6 tables in one query?

Question

Hi, can anyone help me join the 5 tables below in one query? I currently have the query below, but it doesn't seem to work as if it had two products with the same identifier inside the hiring table, all products are returned from the product table, which is obviously wrong.

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 

Table data:

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

The query result currently looks like this:

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

When it should look like this, right? since Prod_ID '1' is hired between dates in the request

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

If someone can help change the withdrawal request as suggested, I would really appreciate it. Because I understand that it should work as it is written?

Answer 1

Your problem is that Prod_Id 1 is both inside and outside these date ranges. Instead, use a subquery to filter out which Prod_Id are in these ranges and exclude them.

This is a simplified version of your query:

 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 

And SQL Fiddle .

Assuming I copied and pasted correctly, this should be your request:

 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 

Hope this helps.

Answer 2

Your left outer join will return null if there is no match, which means you still have a row (without HIRE table data) when the results of this join request are Null:

 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 

Try adding a selection from the hires table (for example, hire.Hire_Start ) to see this, then switch it to the inner join, and I think your problem will be solved.

OR add a WHERE clause to the full query with something like hire.Hire_Start is not null

EDIT

If you change the original request to:

 SELECT hires.Hire_Start, 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 

What is returned in the Hire_Start column?

Then, if you add it to the where clause, you will get the expected result:

 SELECT hires.Hire_Start, 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 WHERE hires.Hire_Start is not null GROUP BY products.prod_id, products.title, products.price, product_types.name 

Finally, by completely disabling Outer Join, does this work as expected?

 SELECT hires.Hire_Start, 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 INNER 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')) GROUP BY products.prod_id, products.title, products.price, product_types.name 

And note: this is OR Hires.prod_ID to indicate that if the result does not return hiring information, it is available, in which case you need to write the request more, like the other answer.

Answer 3

Below is the code that may help you:

 SELECT L.V_PRODUCT_ID "PROD_ID" , L.TITLE "TITLE" , L.PRICE "PRICE" , L.TYPE "TYPE" , S.NAME "SUPPLIERS" FROM (SELECT V_PRODUCT_ID , TITLE , PRICE , TYPE , SUPPLIER_ID FROM ((select p.prod_id v_product_id , p.title TITLE , p.price PRICE , t.type TYPE from products p , products_types t where p.type_id = t_type_id) A JOIN (SELECT PROD_ID VV_PRODUCT_ID , SUPP_ID SUPPLIER_ID FROM PRODUCTS_SUPPLIERS) H ON (A.V_PRODUCT_ID = H.VV_PRODUCT_ID))) L JOIN SUPLLIERS S ON (L.SUPPLIER_ID = S.SUPP_ID); 

Answer 4

SELECT Emp.Empid, Emp.EmpFirstName, Emp.EmpLastName, Dept.DepartmentName From Emp Employee INNER JOIN Department of Department ON Emp.Departmentid = Dept.Departmenttid