Hi SQL Experts,
I have been thinking about the below exercise for a few hours now and the SQL I have written
is so slow that it has not finished in 40 minutes for about 400 000 rows.
I need it much faster. There is the option that I create 3 tables holding the data for A, B and C but...
I thought maybe there is a way without creating 3 tables.
Does anyone have an idea?
SELECT
*
--count the number of TRIAL_NO individually for case a,b and c.
FROM T_STA_ERG STA
LEFT JOIN
(--A the TRIAL Number exists in both cases i.e. with matrix 004 and 001
SELECT
ERG_ID,TRIAL_NO, MATRIX, 'MN' AS TRIAL_CLASS
FROM T_STA_ERG
WHERE SUBSTR(MATRIX,4,3) IN('004','001')
) NM ON NM.ERG_ID =STA.ERG_ID
LEFT JOIN
(--B only Matrix 004 where the TRIAL_NO is NOT in the set of TRIAL_NO where Matrix is 001
SELECT
ERG_ID,TRIAL_NO, MATRIX, 'N' AS TRIAL_CLASS
FROM T_STA_ERG
WHERE SUBSTR(MATRIX,4,3) = '004'
AND TRIAL_NO NOT IN (SELECT TRIAL_NO FROM T_STA_ERG WHERE SUBSTR(MATRIX,4,3) = '001')
)N ON N.ERG_ID = STA.ERG_ID
LEFT JOIN
(--C only Matrix 001 where the TRIAL_NO is NOT in the set of TRIAL_NO where Matrix is 004
SELECT
ERG_ID,TRIAL_NO, MATRIX, 'M' AS TRIAL_CLASS
FROM T_STA_ERG
WHERE SUBSTR(MATRIX,4,3) = '001'
AND TRIAL_NO NOT IN (SELECT TRIAL_NO FROM T_STA_ERG WHERE SUBSTR(MATRIX,4,3) = '004')
)M ON M.ERG_ID = STA.ERG_ID