Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread SQL question
Sat, Dec 2 2006 1:42 PMPermanent Link

Jack Tackett
I have 3 tables
 employee     (email, , , , )
 skills master  (id, , , , ,
 skills detail     (email(key to employee) , id(key to skills master) , , , ,)  

employee is the master to skills detail.

skills master is the master skill list

I want to select criteria for the search..
ex.. skill and skill level

but I want multiple skills to be able to be selected.
example sql I am using.

select distinct *, concat(cast(id as char(20)) with '_'+cast(s.skilllevel as char(20))) as nw
from "skill_detail" s, "emp_master" m , "skill_list" l
where ((m.email = s.email) and (s.id=l.id)) and (
(s.id=20 and s.skilllevel >= 0)
or
(s.id=22 and s.skilllevel >= 0)

which works fine but I would like to find all the employees that have both skills not just
anybody that has one of the skills.

any ideas?

Sat, Dec 2 2006 2:34 PMPermanent Link

"Robert"

"Jack Tackett" <jtackett@atcc.net> wrote in message
news:0E22D6B1-1361-4585-9432-BB4064E664A0@news.elevatesoft.com...
>I have 3 tables
>  employee     (email, , , , )
>  skills master  (id, , , , ,
>  skills detail     (email(key to employee) , id(key to skills master) , ,
> , ,)
>
> employee is the master to skills detail.
>
> skills master is the master skill list
>
> I want to select criteria for the search..
> ex.. skill and skill level
>
> but I want multiple skills to be able to be selected.
> example sql I am using.
>
> select distinct *, concat(cast(id as char(20)) with '_'+cast(s.skilllevel
> as char(20))) as nw
> from "skill_detail" s, "emp_master" m , "skill_list" l
> where ((m.email = s.email) and (s.id=l.id)) and (
> (s.id=20 and s.skilllevel >= 0)
> or
> (s.id=22 and s.skilllevel >= 0)
>

Make a temporary table, and join it to your query

SELECT INTO MEMORY\TEMP
SUM(IF S.ID = 22 THEN 1 ELSE 0) HAS22,
SUM(IF S.ID = 20 THEN 1 ELSE 0) HAS20,
ID
WHERE SKILLLEVEL > 0
GROUP BY ID
HAVING (HAS22 > 0) AND (HAS20 > 0);
select distinct *, concat(cast(id as char(20)) with '_'+cast(s.skilllevel as
char(20))) as nw
from "skill_detail" s, "emp_master" m , "skill_list" l, MEMORY\TEMP T
where ((m.email = s.email) and (s.id=l.id)) and (T.ID = S.ID);

I have not looked at it carefully, but you can probably drop the distinct.
Also, if the tables are big, add a CREATE INDEX step after creating the
memory table.

Robert

Sat, Dec 2 2006 3:24 PMPermanent Link

Jack Tackett
The skill level is also variable
thus
skill 1 could have a level of 4
and skill 2 could have a level of 3

and I want to see all employees that have both skill 1 and skill 2
Sat, Dec 2 2006 3:51 PMPermanent Link

Jack Tackett
Jack Tackett <jtackett@atcc.net> wrote:

The skill level is also variable
thus
skill 1 could have a level of 4
and skill 2 could have a level of 3

and I want to see all employees that have both skill 1 and skill 2
skill 1 and 2 at their respective value or greater skill level also I should say it like this

skill 1 with a level of 3 or higher
skill 2 with a  level of 4 or higher
Image