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

Can someone help out with this query?

Hello,

Im trying to accomplish the following:

SELECT ParentMessage, PostDate
FROM ForumMessages
WHERE (ParentMessage > 0)
UNION
SELECT parentmessage, postdate
FROM forummessages
WHERE parentmessage = 0
ORDER BY PostDate DESC

And that is ALMOST working perfectly. My only problem is with the first group of records from the first SELECT, I want DISTINCT records ONLY on ParentMessage. I dont want the Distinct to also take PostDate into consideration (but I still need the field PostDate).

Basically the above query returns records looking like the following:
26787/1/2004 2:00:46 AM
06/30/2004 12:50:40 PM
06/30/2004 10:10:00 AM
35766/29/2004 10:25:44 PM
35766/29/2004 10:11:58 PM
06/29/2004 10:04:35 AM
35386/25/2004 3:43:54 AM
33686/24/2004 9:19:32 PM
33686/24/2004 8:09:21 PM
33686/24/2004 4:40:40 PM

You can see, half of it is working correctly. I got my list of records, but theres some in there that repeat (i.e. 3576, 3368, etc.) I want the 0s to repeat, as they are Parents, but any time a child shows up I only want 1 reference to it, not duplicates.

IF anyone can help with this, I would greatly appreciate it!!

Thanks in advance for any help!If you want only one per ParentMessage, what do you want to show for the PostDate? To show only the most recent date/time:

SELECT ParentMessage, MAX(PostDate)
FROM ForumMessages
WHERE (ParentMessage > 0)
GROUP BY ParentMessage