SQL Join three(3) tables? - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Join three(3) tables?

Author

Topic

energyshawn
Starting Member

3 Posts

Posted - 2009-08-03 : 13:41:03

I have a web application for recording time, bonuses and commissions for employees. I am having a hard time joining three tables together.

Here are the tables:

employee - provides employee information including the primary key emp_id
emp_timecard - provides recorded time from employees
tc_bon_com - provides bonuses and commissions for employee

I join employee with emp_timecard by joining on emp_id I then use a where statement to select only the records that are between a certain date range. This part works fine but where I run into a problem is when I throw the commission and bonus table into the mix. I need it to join the other tables using the emp_id field but I also need to only pull records that are in the pay_end_date range of the emp_timecard.

Here is my statement, what is happening now is that it is selecting records outside the date range;


sql_select = "" & _ 
" SELECT " & _
" E.first_name, E.last_name, E.userid, E.employee_status, E.filenumber, E.paygroup, C.date, C.day_in, " & _
" C.day_out, E.emp_id, E.employee_type, C.meal_out, C.meal_in, C.pto_hours, C.hol_hours, C.ber_hours, " & _
" C.chb_hours, C.special_hours, C.hours_worked, E.supervisorname, E.term_date, E.user_type_id, " & _
" T.bonus, T.commission, T.status " & _
" FROM employee E " & _ 
" LEFT JOIN emp_timecard C ON " & _
" E.userid=C.userid " & _
" LEFT JOIN tc_bon_com T ON " & _
" E.emp_id = T.emp_id " & _
" AND C.date BETWEEN '"&session("payp_week1_start")&"' AND '"&session("pay_end_date")&"' " & _ 
" ORDER BY E.supervisor_emp_id, E.employee_type, E.user_type_id, E.last_name, E.userid, C.date "

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-03 : 13:57:47

Since you are LEFT OUTER JOINing to tc_bon_com and including the data range criteria as part of your JOIN criteria you are getting all rows from the employee table.

Move the date range criteria to a WHERE clause instead of the JOIN criteria.

Be One with the Optimizer
TG

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-03 : 13:59:01

In your query is no where-clause!
The mistake seems to be that you are joining T with E on emp_id and then expanding the ON-clause with AND C.date between ...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-03 : 13:59:54

No, you're never too old to Yak'n'Roll if you're too young to die.

energyshawn
Starting Member

3 Posts

Posted - 2009-08-03 : 14:32:53

Thank you!

The problem when I try to use it as a WHERE statement I end up only being able to pull records that have a emp_id match from the employee table to the emp_timecard table. I am trying to pull all employee information even if their are no matches. This works if I have the date selection as part of the left join command. Do you know of another way to do this?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-03 : 14:49:11

Then move the c.date criteria into the join criteria of the timecard table. How is date of the tc_bon_com rows correlated? You should include that in the join criteria of the tc_bon_com ON clause.


SELECT 

FROM   employee E
LEFT JOIN emp_timecard C 
       ON E.userid=C.userid
       AND C.date BETWEEN @payp_week1_start AND pay_end_date
LEFT JOIN tc_bon_com T 
       ON E.emp_id = T.emp_id
       and t.???
order by

Be One with the Optimizer
TG

energyshawn
Starting Member

3 Posts

Posted - 2009-08-03 : 15:31:29

Brilliant! Here is the final result, works like a champ.


sql_select = "" & _
	" SELECT " & _
  	" E.first_name, E.last_name, E.userid, E.employee_status, E.filenumber, E.paygroup, C.date, C.day_in, " & _
	" C.day_out, E.emp_id, E.employee_type, C.meal_out, C.meal_in, C.pto_hours, C.hol_hours, C.ber_hours, " & _
	" C.chb_hours, C.special_hours, C.hours_worked, E.supervisorname, E.term_date, E.user_type_id, " & _
	" T.bonus, T.commission, T.status " & _
	" FROM employee E " & _ 
	" LEFT JOIN emp_timecard C ON " & _
    " E.userid=C.userid " & _
    " AND C.date BETWEEN '"&session("payp_week1_start")&"' AND '"&session("pay_end_date")&"' " & _
    " LEFT JOIN tc_bon_com T ON " & _
    " E.emp_id = T.emp_id " & _ 
    " AND T.pay_end_date = '"&session("pay_end_date")&"' " & _
    " ORDER BY E.supervisor_emp_id, E.employee_type, E.user_type_id, E.last_name, E.userid, C.date "

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources