Meet Requirements

[PatPHP]

I tried for hours but could not find any solution in sql. Hopefully this example will help to explain.

 

So what is the SQL Statement to find out if Member 'Pat' meets all the reguirements for 'Training 1'?

 

Here an easier example of the DB:

Skill
ID Name
1  HTML
2  VB
3  PHP
4  Java
5  C#
6  CSS

Member
ID Name
1  Pat
2  Sandra
3  Rolf
4  Justin

MemberSkill
MemberID  SkillID
1         1 
1         2
1         3
1         4

Training
ID Name
1  Training 1
2  Training 3
3  Training 4

TrainingRequirement
TrainingID SkillID
1            1
1            2
1            3
1            5

[aschk]

You want to find all the training requirements that Pat does NOT meet, if there are any then you know he doesn't meet them all.

 

All the Skills for training ID 1.

SELECT SkillID
FROM TrainingRequirement tr
WHERE tr.TrainingID = 1

 

All the Skills that Pat has for Training 1

SELECT ms.SkillID
FROM MemberSkill ms
JOIN TrainingRequirement tr ON ms.SkillID = tr.SkillID
WHERE ms.MemberID = 1 AND tr.TrainingID = 1

 

All the Skills that Pat DOES NOT have for Training 1

SELECT tr.SkillID
FROM MemberSkill ms
RIGHT JOIN TrainingRequirement tr ON ms.SkillID = tr.SkillID AND ms.MemberID = 1 
WHERE tr.TrainingID = 1
AND ms.MemberID IS NULL

 

Or something like that...

 

If you give the schema dump with some data i'll test it fully for you.