Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lsulindy
Starting Member
9 Posts |
 Posted - 2009-10-20 : 21:34:26
|
| I have a table which holds employee job ratings. The table has columns for employee_id, project_no, rating_type, score.There are 6 rating_types: safety, attendance, quality, etc.I want to find an employee's job ratings for a certain project.If the rating table is:employee_id project_no rating_type score1234 555 11 41234 555 12 31234 555 13 21234 555 14 5safety_score attendance_score quality_score4 3 2 etc in one rowIf I do this, I get a cross join effect, but I can't think of how to correctly get what I want.SELECT e.fullname, e.ssn, p.start_date, p.end_date, s.status_desc, sa.score AS safety_score, at.score AS attendance_score, qu.score AS quality_scoreFROM Employee eINNER JOIN EmployeetoProject p ON e.id=p.emp_id and p.project=@projectLEFT JOIN luStatus s ON e.status = s.status_idLEFT JOIN Rating sa ON e.id=sa.emp_id AND sa.project=@project AND sa.rating_type=11LEFT JOIN Rating at ON e.id=at.emp_id AND at.project=@project AND at.rating_type=12LEFT JOIN Rating qu ON e.id=qu.emp_id AND qu.project=@project AND qu.rating_type=13WHERE e.id=@employeeThank you |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
multiple joins leads to cross join effect