Problem getting correct results in a SQL Server Query

[request]

I have the SQL Server query as follows:

(select class_code from class_code where course_code IN
(select '(' + ltrim(rtrim(course_code)) + ')' from employee where employee_code = 'A'))

In the above query, the subquery
(select '(' + ltrim(rtrim(course_code)) + ')' from employee where employee_code = 'A')
will return the value as ('sdd','sdsd','sdsd') since course_code field in employee table has the value as 'sdd','sdsd','sdsd'.

The above QUERY should return records.

However, it seems the above QUERY fail to recognize the result from the subquery. WHY????

Please help.
Thanks.

 

[rac2]

Try it this way -

select class_code from class_code where course_code IN 
(select ltrim(rtrim(course_code)) from employee where employee_code = 'A'))

And all that trimming of course_code in the sub-query, is it not necessary in the main query?

 

[request]

i tried what you had suggested but it still does not work.

 

[tlbroadbent]

Does the data in an employee record contain multiple class codes? If so, you have a denormalized table and it will be difficult to get exact matches. The IN clause requires literals (IN ('sdd','sdsd','sdsd')) or a sub-query as described by Rac2.

You could try a query like the following but it may return erroneous results due to the structure of your data.

Select c.ClassCode, e.EmpID
From ClassCode c, Employee e
Where charindex(c.ClassCode,e.ClassCode)>0 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.