March 3, 2016 at 6:02 am
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
March 3, 2016 at 6:32 am
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
March 3, 2016 at 7:14 am
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