ORACLE SQL - multiple JOINs from same table

Question

I have data related material transactions in one table and log history header data related to materials is in another table and detailed log history data in third table. I'm trying to get different status update dates matched to material table but I get duplicate rows for one material transaction

Original material transaction table:

ORDER_NO	MATERIAL	QTY
0001	MAT01	2
0002	MAT02	5

Original Log History Header transaction table:

ORDER_NO	LOG_ID
0001	1001
0001	1002

Status code 1 refers to Opened and code 2 to Closed

Detailed Log History table:

LOG_ID	STATUS_CODE	DATE
1001	1	11/12/2021
1002	2	15/12/2021

With following SQL query:

SELECT 
       TO_CHAR (m.order_no) order_no,
       m.material,
       a.date opened_date,
       ab.closed_date
  FROM MATERIAL_TRANSACTIONS m
       INNER JOIN HISTORY_LOG t
          ON m.ORDER_NO = t.ORDER_NO
       INNER JOIN HISTORY_LOG_DETAILED a
          ON     t.LOG_ID = a.LOG_ID
             AND a.STATUS_CODE = '1'
       INNER JOIN HISTORY_LOG_DETAILED ab
          ON     t.LOG_ID = ab.LOG_ID
             AND ab.STATUS_CODE = '2'

I get following result:

ORDER_NO	MATERIAL	QTY	OPENED_DATE	CLOSED_DATE
0001	MAT01	2	11/12/2021
0001	MAT01	2		15/12/2021

And I would like to get the status dates to the same row as below:

ORDER_NO	MATERIAL	QTY	OPENED_DATE	CLOSED_DATE
0001	MAT01	2	11/12/2021	15/12/2021

I would appreciate all the help I can get and am very sorry if there already is topic for similar issue.

Martin Schapendonk · Accepted Answer · 2021-12-15 12:39:05Z

1

Your problem occurs because you join the history table, which holds 2 records for the order. You could flatten this if you use 2 inline tables that hold exactly 1 record.

with opened_dates as (
  select h.order_id, d.date
  from history h
  inner join details d on h.log_id = d.log_id and d.status_code = '1'
), closed_dates as (
  select h.order_id, d.date
  from history h
  inner join details d on h.log_id = d.log_id and d.status_code = '2'
)
select to_char (m.order_no) order_no,
       m.material,
       o.date opened_date,
       c.date closed_date
from material_transactions m
join opened_dates o on m.order_no = o.order_no
join closed_dates c on m.order_no = c.order_no
;

answered Dec 15, 2021 at 12:39

Martin Schapendonk

13.6k3 gold badges22 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

realopa Over a year ago

Awesome! This did the trick and query execution was rather quick. Thanks Martin for your help!

Ali Fidanli · Accepted Answer · 2021-12-15 14:38:24Z

1

Just an idea :

I joined HISTORY_LOG and HISTORY_LOG_DETAILED tables to get dates for specific status, and set as OPENED_DATE and CLOSED_DATE (if status 1 , then opened date is DATE column, otherwise set it as 01.01.0001)

After that grouped those records by ORDER_NO and maxed the date values to get actual OPENED_DATE and CLOSED_DATE .

Finally joined this subquery with MATERIAL_TRANSACTIONS table :

SELECT 
       TO_CHAR (M.ORDER_NO) ORDER_NO,
       M.MATERIAL,
       QTY,
       L_T.OPENED_DATE,
       L_T.CLOSED_DATE
  FROM MATERIAL_TRANSACTIONS M  
  INNER JOIN 
  (
  SELECT  L.ORDER_NO , 
 MAX( CASE WHEN LD.STATUS_CODE = 1 THEN LD.DATE ELSE TO_DATE('01.01.0001','dd.mm.yyyy')  END ) OPENED_DATE
MAX( CASE WHEN LD.STATUS_CODE = 2 THEN LD.DATE ELSE TO_DATE('01.01.0001','dd.mm.yyyy')  END ) CLOSED_DATE 
   FROM 
  HISTORY_LOG L 
  INNER JOIN HISTORY_LOG_DETAILED LD ON LD.LOG_ID = L.LOG_ID
 GROUP BY L.ORDER_NO
 ) L_T on L_T.ORDER_NO = M.ORDER_NO

Note: I didnt test it. So there can be small syntax errors. Please check it and for better help add a fiddle so i can test my query

edited Dec 15, 2021 at 14:38

answered Dec 15, 2021 at 12:14

Ali Fidanli

1,3828 silver badges12 bronze badges

2 Comments

realopa Over a year ago

Thanks Ali! Your suggestion did work but executing the query took quite a while so I ended up using Martin's suggestion instead

Ali Fidanli Over a year ago

thanks mate. i am glad it helped. bTW its slow because i forgot to join the subquery with the table :)))) i will update it now

Collectives™ on Stack Overflow

ORACLE SQL - multiple JOINs from same table

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related