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!

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

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?

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,

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.

You could also do it this way:

Select *
Into #TempTable
From Servers
Union All
Select *
From Servers
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.