Retrieving step status in an ssis job

Question

We have different SSIS package that we use in daily tasks (updates, ETL...) and we have a kind of complicated structure, where a package calls different other packages. And there are primarily about 10 principal jobs that call secondary ones. So these 10 jobs are always on success even if a step fails so it wouldn't block other executions. Although we would like to retrieve the steps (and their status) that are related to these jobs via a SQL Query but we couldn't join between the steps and their calling jobs and at the same time retrieve the status (The step status in this case and not the jobs).

I searched a lot on the net and i always find a script that joins the steps and calling jobs without the status or steps and status without knowing which job is calling...

(for example this link and this one )

so to sum it all up, we are trying to do a Query where we can join the jobs, their Status and their parent job.

Any help in this matter would be really appreciated and thanks in advance.

EDIT

Thanks to the link in @BaconBits comment i was able to create a query joining three tables (msdb.dbo.sysjobsteps, msdb.dbo.sysjobs, msdb.dbo.sysjobhistory) that retrieves something like the following:

Job_name1 Step_name1  Job1_status
Job_name1 Step_name2  Job1_status
Job_name1 Step_name3  Job1_status
Job_name2 Step_name1  Job2_status
Job_name2 Step_name2  Job2_status

But I still couldn't retrieve the step status (which is what i need in this case since the job outcome is always on success even if a step fails)

Query:

select j.name, s.step_name, 
    CASE WHEN s.last_run_outcome=0 THEN 'Failed'
        WHEN s.last_run_outcome=1 THEN 'Success'
       WHEN s.last_run_outcome=2 THEN 'Retry'
      WHEN s.last_run_outcome=3 THEN 'Canceled'
    END
,h.run_date, s.output_file_name
from msdb.dbo.sysjobsteps s
inner join msdb.dbo.sysjobs j on s.job_id=j.job_id
inner join msdb.dbo.sysjobhistory h
on h.job_id=j.job_id or s.step_id=h.step_id
--where j.name  like '%Dem%'
order by h.run_date, j.name

Thank you @BaconBits and anyone for any further help.