Hi,

I have the following requirement:

a table stores employee skills, with each skill as a separate record, so
employee ----> skill  master detail relationship.

I want to only select employees who have all 3 skills

so for example, if I'm looking for skills A,B,C I want to a return set that
only has one record for each employee who have all the skills.

An SQL can do this?  Happy to use memory tables if necessary for complex
statements

Thanks

Alex

"Al VAs" <deleteprefix_alex@favour.com.au> wrote in message
news:EB758EDE-114A-43E6-B5E3-514763C441A6@news.elevatesoft.com...
> Hi,
>
> I have the following requirement:
>

select id, sum(if (skill = 1 then 1 else 0) ) skill1, sum(if(skill=2 then 1
else 0)) skill2
into memory\temp
from skills
having skill1 > 0 and skill2 > 0
group by id;
select employee.* from employee e
join memory\temp t on e.id = t.id;

Robert

> a table stores employee skills, with each skill as a separate record, so
> employee ----> skill  master detail relationship.
>
> I want to only select employees who have all 3 skills
>
> so for example, if I'm looking for skills A,B,C I want to a return set
> that only has one record for each employee who have all the skills.
>
> An SQL can do this?  Happy to use memory tables if necessary for complex
> statements
>
> Thanks
>
> Alex
>
>
>

Thanks Robert Ill give it a go

Just quickly is there any difference with:

into memory\temp   AND

into memory temp

Ive always used the latter

Regards

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:49D2DD9D-514D-4002-9175-1856FBB2C72D@news.elevatesoft.com...
>
> "Al VAs" <deleteprefix_alex@favour.com.au> wrote in message
> news:EB758EDE-114A-43E6-B5E3-514763C441A6@news.elevatesoft.com...
>> Hi,
>>
>> I have the following requirement:
>>
>
> select id, sum(if (skill = 1 then 1 else 0) ) skill1, sum(if(skill=2 then
> 1 else 0)) skill2
> into memory\temp
> from skills
> having skill1 > 0 and skill2 > 0
> group by id;
> select employee.* from employee e
> join memory\temp t on e.id = t.id;
>
> Robert
>
>> a table stores employee skills, with each skill as a separate record, so
>> employee ----> skill  master detail relationship.
>>
>> I want to only select employees who have all 3 skills
>>
>> so for example, if I'm looking for skills A,B,C I want to a return set
>> that only has one record for each employee who have all the skills.
>>
>> An SQL can do this?  Happy to use memory tables if necessary for complex
>> statements
>>
>> Thanks
>>
>> Alex
>>
>>
>>
>
>

"Al VAs" <deleteprefix_alex@favour.com.au> wrote in
news:EB758EDE-114A-43E6-B5E3-514763C441A6@news.elevatesoft.com:

> Hi,
>
> I have the following requirement:
>
> a table stores employee skills, with each skill as a separate record,
> so employee ----> skill  master detail relationship.
>
> I want to only select employees who have all 3 skills
>
> so for example, if I'm looking for skills A,B,C I want to a return set
> that only has one record for each employee who have all the skills.
>
> An SQL can do this?  Happy to use memory tables if necessary for
> complex statements
>
> Thanks
>
> Alex
>
>
>

SELECT Skills.EmployeeID, COUNT(*) NbSkills
FROM Skills
WHERE Skills.SkillID IN (<Skill_1>,<Skill_2>,<Skill_3>)
GROUP BY Skills.EmployeeID
HAVING COUNT(*) = 3

If you don't want the count field, you'll need:

SELECT Skills.EmployeeID, COUNT(*) NbSkills
INTO "\memory\tmpTbl"
FROM Skills
WHERE Skills.SkillID IN (<Skill_1>,<Skill_2>,<Skill_3>)
GROUP BY Skills.EmployeeID
HAVING COUNT(*) = 3;

SELECT EmployeeID
FROM "\memory\tmpTbl"

and if you only have 3 skills you can leave out the WHERE line.
--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.24 Build 1)

"Al Vas" <forgetit@forgetit.com> wrote in message
news:7DA05089-4510-4A80-BBBA-C47075103E44@news.elevatesoft.com...
> Thanks Robert Ill give it a go
>
> Just quickly is there any difference with:
>
> into memory\temp   AND
>
> into memory temp
>

V4 vs V3 syntax

Robert