I have following tables
tasks
id | name | proj_id
1 | task1 | 1
2 | task2 | 1
3 | task3 | 1
projects
id | name
1 | sample proj1
2 | demo project
budget_versions
id | version_name| proj_id
1 | 50 | 1
budgets
id | cost | budget_version_id | task_id
1 | 3000 | 1 | 2
2 | 5000 | 1 | 1
I need to join these tables to get a result as below
task_id | task_name | project_id | budget_version | budget_id | cost
1 | task1 | 1 | 1 | 2 |5000
2 | task2 | 1 | 1 | 1 |3000
3 | task3 | 1 | NULL | NULL |NULL
select tsk.id,tsk.name, tsk.project_id, bgtver.id, bgt.id, bgt.cost
from TASK tsk
left outer join BUDGET_VERSIONS bgtver
on tsk.project_id= bgtver.project_id
left outer join BUDGETS bgt
on bgtver.id = bgt.budget_version_id and tsk.id = bgt.task_id
where bgtver.id = 1
