Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
d
Starting Member
26 Posts |
 Posted - 2001-12-17 : 13:28:30
|
| I have a table called as course in SQL Server 7.0 database.There is another table called as employee.The table course has fields called course_code, course_code_group which is defined as char(150).e.g. for course_code = 'A', course_code_group = 'ASF','SDSDF','SDSD'SO, the field course_code_group contains comma separated single quoted values.The table employee has fields employee_code and course_code_group.Now, my query is as follows:select employee_code from employee where employee_code_group in(select course_code_group from course wher course_code = 'A')This query returns me no values even though it should. WHY???? Is there a problem because of the way I have the values in the field course_code_group in the table course??Please help.Thanks. |
|
|
TonyH
Starting Member
29 Posts |
Posted - 2001-12-17 : 14:00:56
|
| Hi,1) Do you mean to say 'where course_code_group in' etc , not 'where employee_code_group in' etc, as employee_code_group does not exist in the employee table?2)If the course_code field in the employee table is in fact only one of the strings in the course_code_group field in the course_code table, e.g. 'ASF' as in your example, then use the charindex function to determine whether the employee course_code string appears in the course_code_group string in the course_code table.If you split the different strings in the course_code_group column of the course table into individual rows. then you existing query might work (assuming 1) above).CheersTonyHwww.SQLCoder.com - Code generation for SQL Server 7/2000 |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-17 : 14:02:23
|
| Yes. The IN statement does not work this way. What you're really wanting to do is some string comparisons. BUT I would encourage you to change the table structure instead. Rather than have a multi-valued field (course code group), create a new table and make a 1-to-many or many-to-many relationship. This will make your lookup code LOTS easier and faster.-------------------It's a SQL thing... |
|
|
|
d
Starting Member
26 Posts |
Posted - 2001-12-17 : 17:47:14
|
| Thanks a lot for your responses.I will do as per your suggestion.ThanksEdited by - d on 12/17/2001 17:49:31 |
|
|
|
|
|
|
|
|
Problem Getting Results In a QUERY