Left Join question

[jmaze]

I have 2 tables I'm dealing with:
Training - employees' training (only has data when a course has been
completed)
Project - list of required courses an employee needs to be able to be
involved with the project
I need a query (or report) to show every course in "Project" and then I want
to see whether or not an employee has taken all the required courses. Right
now my query only shows me courses for an employee if they have a
corresponding date. I want to see null values for dates, too, but an "is
null" constraint yeilds nothing. Is there a way to get something like this?:
First Aid...John Doe...1/1/2008
CPR.........John Doe...
AED.........John Doe...1/1/2008

My Query SQL:
SELECT DISTINCT tblProject.course_number, tblProject.course_title,
tblTraining.employee_id, tblTraining.employee_name,
Max(tblTraining.completion_date) AS MaxOfcompletion_date
FROM tblProject LEFT JOIN tblTraining ON (tblProject.course_number =
tblTraining.course_number) AND (tblProject.course_title =
tblTraining.course_title)
GROUP BY tblProject.course_number, tblProject.course_title,
tblTraining.employee_id, tblTraining.employee_name;

 

[Rick Brandt]

I have 2 tables I'm dealing with:
Training - employees' training (only has data when a course has been
completed)
Project - list of required courses an employee needs to be able to be
involved with the project
I need a query (or report) to show every course in "Project" and then
I want to see whether or not an employee has taken all the required
courses. Right now my query only shows me courses for an employee if
they have a corresponding date. I want to see null values for dates,
too, but an "is null" constraint yeilds nothing. Is there a way to
get something like this?: First Aid...John Doe...1/1/2008
CPR.........John Doe...
AED.........John Doe...1/1/2008

My Query SQL:
SELECT DISTINCT tblProject.course_number, tblProject.course_title,
tblTraining.employee_id, tblTraining.employee_name,
Max(tblTraining.completion_date) AS MaxOfcompletion_date
FROM tblProject LEFT JOIN tblTraining ON (tblProject.course_number =
tblTraining.course_number) AND (tblProject.course_title =
tblTraining.course_title)
GROUP BY tblProject.course_number, tblProject.course_title,
tblTraining.employee_id, tblTraining.employee_name;

Looks to me like you need a third table (Employees), that you can also outer
join to.

 

[Lord Kelvan]

jesus well you should have course title in training as well that is
most likly your first problem

second you should need a thrid table an associtive entity

because as it stands each employee can only do one project unless you
are entering first aid in the project table multiple times what you
need is a table that joins the two tables and has the course_number
and the employee_id in it

but am i correct to assume you have firstaid multiple times in the
project table so that multiple people can do the first aid course

if this is the case then you cannot do yoru left join or any real
meaningful join because RDB design cannot handle Many to Many
releationships

if you created the third table then you could easly do what you want

you woudl have three tables

tblproject
course_number
course_name
....

tblproject_training
course_number
employee_id
completion_date

tbltraining
employee_id
employee_name
....

doing that will allow you to attempt the query tell me if you think
this is a plan

 

[Lord Kelvan]

jesus well you should have course title in training as well that is

that shoudl be shouldnt sorry