Creating Tables

[Richard]

I am new at the development stages of my DBA career and I am stumped on how to approach this problem, any help would be great.

Problem: Our web developer is designing a Software survey that ranks the difficulty of tasks within a software application like MS Word. Each of these tasks have a rank tied to them.

Here are the tables that he created.
Table 1
EmpID Text
123456 A1, A2, A3


Table 2
ValueID Text Rank
A1 Create Table 1
A2 Insert Query 5

In the text field of table 1 he inserts every task or valueid that the employee is able to do.

He needs to be able to select Empid, text(from table 2), and Rank. Based on need. An example would be 'Which employees can Insert a query'. And he would need to be able to look in the text string on Table 1 and find every A2 in listed then get empid and rank. I know that it can be done with full text searching, however I guess what I want to know is if there is another way to create tables that would jus make a join necessary where the value id can be tied to the empid for each task the employee is able to do?

I hope that this makes sense!!

[MAK]

Here we go. I beleive I did it right.!!!!

--drop table Table1
create table Table1 (EmpID int, Text char(20))
--drop table Table2
create Table Table2 (ValueID char(5), Text char(20), Rank int)

insert into table1 select 123456,"A1,A2,A3"
insert into table1 select 123457,"A1,A3"
insert into table1 select 123458,"A2"
insert into table1 select 123459,"A1,A2"

insert into table2 select "A1","Create Table", 1
insert into table2 select "A2","Insert Query", 5
insert into table2 select "A2","Select Query", 8



select A.empid,B.rank,B.valueid,B.text from table1 A, table2 B
where PATINDEX ( "%"+ltrim(rtrim(B.valueid))+"%" , A.text) >0 and
B.valueId ="A1"

select A.empid,B.rank,B.valueid,B.text from table1 A, table2 B
where PATINDEX ( "%"+ltrim(rtrim(B.valueid))+"%" , A.text) >0 and
B.valueId ="A2"





------------
Richard at 5/6/2002 2:35:14 PM

I am new at the development stages of my DBA career and I am stumped on how to approach this problem, any help would be great.

Problem: Our web developer is designing a Software survey that ranks the difficulty of tasks within a software application like MS Word. Each of these tasks have a rank tied to them.

Here are the tables that he created.
Table 1
EmpID Text
123456 A1, A2, A3


Table 2
ValueID Text Rank
A1 Create Table 1
A2 Insert Query 5

In the text field of table 1 he inserts every task or valueid that the employee is able to do.

He needs to be able to select Empid, text(from table 2), and Rank. Based on need. An example would be 'Which employees can Insert a query'. And he would need to be able to look in the text string on Table 1 and find every A2 in listed then get empid and rank. I know that it can be done with full text searching, however I guess what I want to know is if there is another way to create tables that would jus make a join necessary where the value id can be tied to the empid for each task the employee is able to do?

I hope that this makes sense!!