Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 4 of 4 total |
SQL question |
Sat, Dec 2 2006 1:42 PM | Permanent 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 PM | Permanent 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 PM | Permanent 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 PM | Permanent 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 |
This web page was last updated on Wednesday, March 27, 2024 at 05:29 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |