Can this be done??

Hi,
Can anyone give me some advice on how I can accomplish the following.

I have a table that has a value like the following "2010302NOV01222004"

The above value is made of of 3 distinct values
They are:
Employee Code - 2010302
Course Code - Nov012
Quarter and Year: 22004

In another table I have a set of values that relate to the middle part of the above value (Course Code), i would like to return the course name that relates to the course code from the other table.

I have been able to extract the coursecode using the following code but can't see how to pass vthe value to the courseType table to return my CourseName value.

<code>
SUBSTRING(dbo.Leave.Training, 8, LEN(dbo.Leave.Training) - 12)
</Code
I would need to do this from a stored procedure.

Regards..
Peter.

You can join your Leave table to the CourseType table in order to pull the CourseName for every Training field value using something like the following:

SELECT Leave.Training, CourseType.CourseCode, CourseType.CourseName
FROM Leave JOIN CourseType
ON SUBSTRING(dbo.Leave.Training, 8, LEN(dbo.Leave.Training) - 12) = CourseType.CourseCode

I'm not sure what you're trying to accomplish with the stored proc, so I won't go into details on using parameters, etc...

|||Jason,
Thanks for the reply, what i am trying to accopmlish is:

1. I have a table that lists all the leave employees take, this includes any training. I need all records returned from the leave table and those that have entries in the training filed of the leave table require the courseName to be returned from the Course type Table.

The training is listed in the leave table as described previously and I need to extract the training CourseID from that field, as you saw "2010302NOV01222004" is in the training field in the leave table.

I need to extract "NOV012" from that filed and get the coursename (Novell iChain 2.2) returned from the Course Type table, if the field is null then ignore it.

Hope this expalins better what i am trying to accomplish.

Regards..
Peter.|||

Try executing the following to see if it doesn't give you exactly what you asked for:

SELECT Leave.*, CourseType.CourseCode, CourseType.CourseName
FROM Leave LEFT JOIN CourseType
ON SUBSTRING(dbo.Leave.Training, 8, LEN(dbo.Leave.Training) - 12) = CourseType.CourseCode

|||Jason,
Thanks that has hit the nail on the head.. Exactly what i needed...

Regards..
Peter