June 12, 2012 at 10:24 am
I have an issue trying to complile results on staff training, show who has and who hasn't undertaken certain training courses.
This is what I'm doing at the moment
Select a.trainingcourse, c.name, c.personID, b.expirydate
from trainingcourses a
left join trainingcourselog b on b.courseID = a.courseID
left join staff c on c.staffID = b.staffID and c.staffID = '1234'
where a.courseID in ('id1','id2','id3')
The results I get...
Training 1 John Smith 1234 05/08/2012
Training 2 John Smith 1234 05/08/2012
Training 3 null null null
As John Smith hasn't done Training Course 3 it appears as null
What I want to get is...
Training 1 John Smith 1234 05/08/2012
Training 2 John Smith 1234 05/08/2012
Training 3 John Smith 1234 null
I need to get the persons name and id to appear against the training courses they haven't done.
Any ideas?
June 12, 2012 at 10:28 am
To provide you with the best help possible we really need more information. Please read and follow the instructions in the first article I reference below in my signature block. It will walk you through what to post and how to post it. With that information you will get much better answers to your questions.
June 12, 2012 at 10:29 am
If you would provide the setup (ddl & sample data) I could test my query, but...
Select a.trainingcourse, c.name, c.personID, b.expirydate
from trainingcourses a
cross join staff c
left join trainingcourselog b on b.courseID = a.courseID
and b.staffID = c.staffID
where a.courseID in ('id1','id2','id3')
June 12, 2012 at 10:51 am
I don't have access to a SQL Server right now, so I'm not able to test if this will work, or if it performs very well, but I'll give it a try anyway.
Select a.trainingcourse, c.name, c.personID, b.expirydate
from trainingcourses a
cross join staff c
left join trainingcourselog b on b.courseID = a.courseID and c.staffId=b.staffId
where a.courseID in ('id1','id2','id3') and c.staffID = '1234'
No guarantee that this will compile.
June 13, 2012 at 3:23 am
Nils Gustav Stråbø (6/12/2012)
I don't have access to a SQL Server right now, so I'm not able to test if this will work, or if it performs very well, but I'll give it a try anyway.
Select a.trainingcourse, c.name, c.personID, b.expirydatefrom trainingcourses a
cross join staff c
left join trainingcourselog b on b.courseID = a.courseID and c.staffId=b.staffId
where a.courseID in ('id1','id2','id3') and c.staffID = '1234'
No guarantee that this will compile.
That worked a treat, thank you.
Sorry for not posting the code in the correct way. Haven't posted much on here and was in a bit of a rush. Will remember for next time.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply