Post reply

Need help in extracting data

  • January 5, 2006 at 8:41 pm

    #103020

    Hello Friends,

    I need a small help in extracting data from one table

    Details.

    There is a skillset master and employee skillset

    Employee skillset is having primary and secondary skills

    Skillset master has skills id and name

    Problem is at i want to extract all the secondary skillset of an employee.

    I wrote a query to fetch the data as below

    select skill_name from tblskillsmaster where cast(skill_id as varchar(30)) in (

    select '''' + REPLACE( secondary_skill, ',' , ''',''') + '''' from tblskillset where empid='EM100330'

    )

    It is not returning any data

    The data sample is

    Skillsetsmaster

    Skill_ID Skill_Name

    ------------ --------------------------------------------------

    2 Confirmit

    3 Microsoft Technologies

    4 SQL Server 2000

    10 MR Interviews

    empID Primary_skill Secondary_skill

    ---------------------------------------------------

    EM100251 4

    EM100330 3 3,4

    EM100008 3 3,4

    Please help me friends

    I am in urgent need

    Thank you

    Albert

    http://www.bepenfriends.com

  • January 6, 2006 at 1:28 am

    #613114

    Firstly, I would suggest a table redesign, if that's possble.

    EmployeeSkills would be much, much better as below.

    CREATE TABLE EmployeeSkillset (

     EmpID CHAR(8) NOT NULL,

     SkillID INT NOT NULL,

     IsPrimarySkill BIT NOT NULL DEFAULT (0)

     CONSTRAINT PK_EmployeeSkills PRIMARY KEY (EmpID, SkillID)

    )

    That said, dynamic SQL will work here. The IN clause doesn't work the way you're trying to use it. It needs either a hard coded list or a recordset.

    DECLARE @skills VARCHAR(10)

    SELECT @skills = secondary_skill FROM tblskillset WHERE EmpID='EM100330'

    EXECUTE ('SELECT skill_name FROM tblskillsmaster WHERE SkillID IN (' + @skills + ')')

    There are other ways most of which I've seen involve splitting the list into a temp table and using that as a join.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 6, 2006 at 6:13 am

    #613186

    Thank you friend. That helped a lot.

    Thanks

    Albert

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply