SQL Selecting from many tables

Question

I have three tables 'projects', 'employees' and bridge table 'project_employees'.
In the problem is that project table has salary amout for each project and every employee gets salary according to their allocated projec. in 'project_employees' table there are two columns which represents project id and emoloyee id from these tables. how can I get employees salary by geeting thier allocated query?

Answer 1

Something like this should help you out!

select
emp.id
,sum(pro.salary) "CUM_SALARY"
from employees emp
left outer join project_employees proemp
on emp.id = proemp.emp_id
left outer join projects pro
on pro.id = proemp.pro_id
group by proemp.emp_id

Answer 2

My pleasure, @Ilyosjon

Answer 3

I understand. LEFT OUTER JOIN in this query, having employees table on the left hand side lists all employees and joins additional data to it. If your focussing more on the table project_employees, make the first LEFT OUTER JOIN a RIGHT OUTER JOIN. This should assure that all employees allocated to projects are listed, not more.

Alternatively, add a

where sum(pro.salary) is not null

Answer 4

thank you @senfman It worked but it returns one emloyee which is not allocated to any project and naturally his salary is null. how can I fix this?

Answer 5

Thank you very much @sefman you saved the day!!! :D