Oracle Query Tuning (Duplicate table access)

Question

I'm trying to further tune this query. The query returns the status for three different tests for each sample. However, if I wish to further filter the samples returned, I have to put the conditions in both 'SELECT ... FROM sample ...' queries.

Can this query be rewritten referencing the sample table only once?

SELECT   sample_id,
         created_on,
         s_acid,
         s_ph,
         s_titr
  FROM 
      (SELECT sample_id, -- Rows w/ same sample_id to columns
              MAX (CASE WHEN tst_tmpl_id = 36 THEN status END) AS s_acid,
              MAX (CASE WHEN tst_tmpl_id = 43 THEN status END) AS s_ph,
              MAX (CASE WHEN tst_tmpl_id = 66 THEN status END) AS s_titr
         FROM (SELECT test_id, test_tmpl_id, sample_id FROM test)
         JOIN (SELECT sample_id      FROM sample WHERE sam_tmpl_id = 18)
              USING (sample_id)
         GROUP BY   sample_id
       )
        -- get other sample fields 
  JOIN (SELECT sample_id, created_on FROM sample WHERE sam_tmpl_id = 18)
       USING (sample_id)

Answer 1

WITH q AS
        (
        SELECT  sample_id, created_on
        FROM    sample
        WHERE   sam_tmpl_id = 18
        )
SELECT  sample_id,
        created_on,
        s_acid,
        s_ph,
        s_titr
FROM    (
        SELECT  sample_id, -- Rows w/ same sample_id to columns
                MAX (CASE WHEN tst_tmpl_id = 36 THEN status END) AS s_acid,
                MAX (CASE WHEN tst_tmpl_id = 43 THEN status END) AS s_ph,
                MAX (CASE WHEN tst_tmpl_id = 66 THEN status END) AS s_titr
        FROM    (
                SELECT  test_id, test_tmpl_id, sample_id
                FROM    test
                )
        JOIN    q
        USING   (sample_id)
        GROUP BY
                sample_id
        )
JOIN    q
USING   (sample_id)