Left Join

[bhanugnv]

Hi
I am trying to use left join on the following tables.
Employees
----------
EMPLOYEE_ID
FIRST_NAME
LAST_NAME

and

Weekly_Timesheets
-------------------
EMPLOYEE_ID
WEEK_NO
YEAR_NO
TOTAL_HOURS_WORKED

All I want is to pull all the employees with their corresponding total worked hours. There are cases when some employees forget to fill their time sheets in a week and might not have entries in weekly_timesheets table. Since I want all the employees irrespective of whether they have a record in weekly_timesheets or not I am using left join.

The left join I am using is as follows:

SELECT EMPLOYEES.EMPLOYEE_ID,
WEEKLY_TIMESHEETS.TOTAL_HOURS_WORKED
FROM EMPLOYEES
LEFT JOIN WEEKLY_TIMESHEETS
ON WEEKLY_TIMESHEETS.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID
AND WEEKLY_TIMESHEETS.WEEK_NO = 46;


The moment I am trying to execute this statement Access is crashing. I couldn't figure out what am I doing wrong.

Kind Regards
Bhanu

 

[neileg]

Should that AND be a WHERE

 

[bhanugnv]

I don't think.

Where will only give me all the employees who have records in weekly_timesheets for week 46 and not those who have missed filling up their timesheets.

Bhanu

 

[neileg]

It should be a WHERE, I was being polite!

If you want nulls too, this should be the where cluase:
WHERE Weekly_Timesheets.WEEK_NO=46 OR Weekly_Timesheets.WEEK_NO Is Null;

 

[bhanugnv]

Hi Neil,

Sorry if my earlier reply sounded rude.

This is the sample data in both the tables.

EMPLOYEES
---------
EMPLOYEE_ID admin
FIRST_NAME Admin
LAST_NAME User

EMPLOYEE_ID user
FIRST_NAME USER FN
LAST_NAME USER LN


WEEKLY_TIMESHEETS
-----------------
EMPLOYEE_ID user
WEEK_NO 46
YEAR_NO 2005
TOTAL_HOURS_WORKED 320


And I am looking for this result

EMPLOYEE_ID WEEK_NO YEAR_NO TOTAL_HOURS_WORKED
admin 46 2005 0
user 46 2005 320

Using where will never give the admin record and will only give me
EMPLOYEE_ID WEEK_NO YEAR_NO TOTAL_HOURS_WORKED
user 46 2005 320

Using left join should give me (in theory)
EMPLOYEE_ID WEEK_NO YEAR_NO TOTAL_HOURS_WORKED
admin 46 2005 0
user 46 2005 320

But the Access just crashes when using the sql statement.

SELECT EMPLOYEES.EMPLOYEE_ID, WEEKLY_TIMESHEETS.WEEK_NO, WEEKLY_TIMESHEETS.YEAR_NO, WEEKLY_TIMESHEETS.TOTAL_HOURS_WORKED
FROM EMPLOYEES
LEFT JOIN WEEKLY_TIMESHEETS
ON (WEEKLY_TIMESHEETS.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID
AND WEEKLY_TIMESHEETS.WEEK_NO = 46);

Thanks
Bhanu

 

[bhanugnv]

Hi Neil,

Missed columns WEEKLY_TIMESHEETS.WEEK_NO, WEEKLY_TIMESHEETS.YEAR_NO in earlier sql statement.

Finally got the required result while experimenting.

SELECT EMPLOYEES.USER_ID, WEEKLY_TIMESHEETS.WEEK_NO, WEEKLY_TIMESHEETS.YEAR_NO, WEEKLY_TIMESHEETS.TOTAL_HOURS_WORKED
FROM EMPLOYEES LEFT JOIN WEEKLY_TIMESHEETS ON (WEEKLY_TIMESHEETS.EMPLOYEE = EMPLOYEES.USER_ID
AND (WEEKLY_TIMESHEETS.WEEK_NO = 46 OR Weekly_Timesheets.WEEK_NO Is Null));


Many thanks for the time and help
Bhanu

 

[neileg]

I'm not claiming to be an SQL expert (because I'm not). The WHERE clause is explicitly supported by Access SQL but I can't find any reference to the use of AND as you have used it with the TO clause.

If it works and solves your problem that's fine. I'm just concerned that this might casue a problem at a later stage.

I wasn't suggesting you were rude, by the way. I meant that I really wanted you to use WHERE.