Oracle SQL, how to constrain on join?

Question

This is HP/IBM Quality Centre;

There are three tables.

A testcycl has test and a test has steps. Steps have many duplicates by nature of having many runs.

Therefore, for a given TestCycle, I would like to get all of the tests including their steps, for the most recent st_run_id.

Have been playing around with ways to constrain it, but nothing is working out for me. 1 Cycle could have 500 tests which yield 5000 steps due to duplicate runs, it's really easy to get stuck.

How to constrain the steps query based most recent st_run_id, keeping in mind that it has to be constrained by st_test_id?

select *
from
(
    -- Get details for that test
    select
        ts_test_id,
        ts_name,
        ts_description
    from test
    where 1=1
    and ts_test_id in
    (
            -- Get all test ID's belonging to a specific cycle
            select distinct tc_test_id
            from testcycl
            where 1=1
            and testcycl.tc_assign_rcyc = 1021
    )
) tests
-- Get the steps
left join
(
    select
        step.st_test_id,
        step.st_run_id,
        step.st_execution_date,
        step.st_id,
        step.st_step_name,
        step.st_description,
        step.st_expected
    from step
    where 1=1
) steps
on tests.ts_test_id = steps.st_test_id

Is there a reason why you're doing sub-queries? I.e., maybe I'm not understanding, but why not join the `step` table directly to `tests` and `testcycl`? Maybe an `inner join` would be preferred too. But I have very little sense of what your data look like, so maybe I'm off the mark. `select * from test tests inner join step steps on tests.ts_test_id = steps.st_test_id inner join testcycl on tests.ts_test_id = testcycl.tc_test_id where testcycl.tc_assign_rcyc = 1021;` — Marc, Jul 10 '20 at 00:09
I cannot get it to work. testcycl.tc_assign_rcy=42 has test.ts_test_id's (A, B, C, D, E...) but it only returns A. I do not understand why. Tried other joins like full outer, same result. — HandsomeRob, Jul 10 '20 at 00:26

score 0 · Answer 1 · answered Jul 10 '20 at 00:43

0

This should do what you want:

left join
(select s.*,
        row_number() over (partition by s.st_test_id order by st.st_run_id desc) as seqnum
) steps
on tests.ts_test_id = steps.st_test_id and steps.seqnum = 1

answered Jul 10 '20 at 00:43

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Jul 10 '20 at 05:41

You should use direct tables instead of subquery as follows:

Select * from
(select t.*, s.*,
        row_number() over (partition by s.st_test_id order by s.st_run_id desc nulls last) as rn
    from test t 
    Left join steps s on t.ts_test_id = s.st_test_id 
    where 1=1
    and t.ts_test_id in
    (
            -- Get all test ID's belonging to a specific cycle
            select distinct tc_test_id
            from testcycl
            where 1=1
            and testcycl.tc_assign_rcyc = 1021
    )
) Where rn = 1;

You can take only that columns in select clause which you require.

Oracle SQL, how to constrain on join?

2 Answers2