Join three ( 3 ) tables SQL Help

[shawn.bordeaux]

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 "

Can anyone point out where I am going wrong here? Thank you!

[sknake]

Please clean up your queries before you post them to this forum :)

The problem seems to be you have this in the wrong location:

AND C.date BETWEEN '&session(payp_week1_start)&' AND '&session(pay_end_date)&

That is a condition for the third join in your query and should be a condition for the second join.

Try this:

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  
 ORDER BY E.supervisor_emp_id, E.employee_type, E.user_type_id, E.last_name, E.userid, C.date

[Smirgu]

I'm trying to do a similar task that perfectly fits the topic so I'll ask it here: I want to create a new datatable that is a direct combination of three tables (column names are the same). I have very little experience with SQL so what I tried was the following but it didn't work:

CREATE TABLE table_all ( SELECT * FROM table_1 UNION ALL SELECT * FROM table_2 UNION ALL SELECT * FROM table_3)

Any help?

[sknake]

Smirgu,

Please create a new thread with your question and I will be happy to help you out. To keep Daniweb organized you should ask questions in your own threads! :)

[Smirgu]

Smirgu,

Please create a new thread with your question and I will be happy to help you out. To keep Daniweb organized you should ask questions in your own threads! :)

Okay. It's probably more controlled in the way that you see which problems have been solved. I'll do that next time.

Any way, I got a solution for it. I just had to use insert and put all the column names in the query, which would have been a bit gnarly if the Management Studio software wasn't able to produce as list of those columns automatically. So the query did not look that neat, but it did the trick.

[sknake]

You could also do it this way:

Select *
Into #TempTable
From Servers
Union All
Select *
From Servers