multiple rows in where clause to filter employee with more skills

  • Hi,

    I have a table for employees with skills in different Levels. In the UI you can choose numerous skills with a Level to find the appropriate employee.

    I wrote a query to return the employee but wondering if there isn't any better solution for it?

    Help appreciated! 😀

    Thanks, Sue

    create table tblSkills

    (id int identity(1,1) primary key,

    FKSkill int,

    FKEmployee int,

    lvl tinyint)

    insert into tblskills values

    (1,1,3),

    (2,1,4),

    (3,1,4),

    (1,2,3),

    (3,2,3),

    (1,4,4),

    (3,4,4)

    -- these values can vary and come from UI

    -- can be 2 or 3 or more

    create table #t(id int, lvl tinyint)

    insert into #t values(1,2),(3,3)

    declare @anz int

    select @anz = count(*) from #t

    select FKEmployee

    from tblskills as s join #t as t on t.id = FKSkill and s.lvl > t.lvl

    group by FKEmployee

    having count(*) >= @anz

    Susanne

  • Your query looks fine to me.

    I would move the s.lvl > t.lvl part into a WHERE clause, but it won't have any effect on the results.

    There's also no harm in combining things, like this:

    select s.FKEmployee

    from dbo.tblskills as s join #t as t on t.id = s.FKSkill

    where s.lvl > t.lvl

    group by s.FKEmployee

    having count(*) >= (select count(*) from #t)

    Notes

    - Always use aliases for columns, unless you are selecting from only one table (I still do it even then)

    - Always schema-qualify your tables

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thanks Phil!

    Susanne

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

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