Click here to Skip to main content
15,878,048 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi guys i have a sql issue, objective is to make 2 tables shown in my test site, 1st to show employees, 2nd to show all their salary which depends on the month i selected.

i have 2 tables :

Employee (Id,Name)

Salary (Id,Name,Salary,EmployeeId,DateId)

now for example, Employee table has 2 persons, Tom and Jerry, Salary table is null
1.how to make query to show salary table with 1st row:
SQL
name Tom,Salary 0,EmployeeId 1,DateId @selectedate
2nd row:
SQL
name jerry,Salary 0,EmployeeId 2,DateId @selectedate



2.and when i add a new employee "mary" in employee table, i also want to show mary with salary 0 in my salary table, how to make it; and also when i delete Tom from employee table, i wont see Tom in my salary table, how to make it? thanks
Posted

1 solution

XML
if i understand your issues, then i think you need to use <b>left outer join</b> in your query.
for example,
<pre lang="SQL">Select employee.name,isnull(salary.salary,0),employee.id from employee left outer join salary on
employee.id = salary.employeeid</pre>

for your second issues, if you insert a value to your first table without inserting value to second table,above sql (left outer join) will return salary as 0.



i think it will solve your issues. let me know, if it not.
 
Share this answer
 
Comments
v03051435 6-Nov-13 11:02am    
@Soumitra, thank you for ur response, yeah, the 1st issue sovled by using left join:
select s.Id,e.Name,COALESCE(s.salary, 0) AS salary,s.employeeId,s.dateId
from employee e
left join salary s on s.employeeId = e.id,

and then i can insert update using query as i like :D

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900