Join problem in SQL statement

[dom24]

Can anyone help me with this statement:

SELECT Staff.StaffNo, Validation.Completed, LName, FName, Staff.ManagerNo, CourseName,
CourseDate, ManagerEvaluation.CourseKey as CourseKey2, ManagerEvaluation.ManagerNo as ManagerNo2,
ManagerEvaluation.StaffNo as StaffNo2, ManagerEvaluation.Completed as Completed2
FROM Validation
INNER JOIN Staff on Validation.StaffNo = Staff.StaffNo INNER JOIN Course on Validation.CourseKey = Course.CourseKey
INNER JOIN ManagerEvaluation on Validation.StaffNo = ManagerEvaluation.StaffNo
where Staff.ManagerNo = '10514T' and ManagerEvaluation.Completed IS NOT NULL

I want to pull back all the records from MangerEvaluation where ManagerEvaluation.Completed IS NOT NULL.

This seems to be pulling back both the NULLS and NOT NULLS.

Thanks.

 

[Lothario]

Try this:
where Staff.ManagerNo = '10514T' and (ManagerEvaluation.Completed IS NOT NULL OR ManagerEvaluation.Completed <> "")

-L

 

[hneal98]

instead of this

and ManagerEvaluation.Completed IS NOT NULL

try this

and isnull(ManagerEvaluation.Completed,'X')<> 'X'

 

[dom24]

No doesn't like it. Error message is:

Cannot use empty object or column names. Use a single space if necessary.

I think i've got something wrong with the joins actually cos it's pulling back the data even without the WHERE clause. It's pulling all data from the Validation table AND the ManagerEvaluation table.

 

[Lothario]

If you just want to get records from the ManagerEvaluation table then use a RIGHT JOIN instead of INNER JOIN.

-L

 

[dom24]

hneal 98 - won't work cos it's a date field.
Lothario - use a right join where? On all the joins?

 

[Lothario]

On just the last one..something like this:

RIGHT JOIN ManagerEvaluation on Validation.StaffNo = ManagerEvaluation.StaffNo

-L

 

[dom24]

Brings back exactly the same.

 

[Lothario]

May be its better that you provide some data and the kind of result you are looking for.

that way someone here can suggest you in the right way.

-L

 

[hneal98]

then change it to

and isnull(ManagerEvaluation.Completed,'12/31/2078')<> '12/31/2078'