Query Who is Not Trained in a Skill Set

[londo]

I am really stuck on what should be simple i just can`t find the correct query to find the results i am after i have 3 tables

EmployeeT (Table Name)	ModulesT (TableName)	SkillT (Table Name)
EENumber PK	ModulesID PK	SKillID PK
FirstName	EENumber FK	Description
LastName	SkillID FK
	Mod_Inactive

That is my table layout what i am trying to accomplish is any employee (EENumber) that has not been trained in a skill (SkillID) set so after a query that will output something like this

EENumber	FirstName	LastName	Description
1	Joe	Bloggs	Skill 1
1	Joe	Bloggs	Skill 2
1	Joe	Bloggs	Skill 3
2	Tony	Blank	Skill 1
2	Tony	Blank	Skill 3
3	Paul	User	Skill 2

so Joe Bloggs is not trained in skill 1 2 or 3
Tony Blank is not trained in Skill 1 and 3 but trained in skill 2 so not show skill 2 in the list
Paul User is not trained in Skill 2 but trained in skill 1 and 3 so not show skill 1 and 3 in the list

I have tried different things cant seem to get this right

this query gives me all the employees who are trained i can`t work out how to get who is NOT trained in a skill from the training list in SkillT table

SELECT E.EENumber, E.FirstName, E.LastName, S.Description, M.Mod_Inactive
FROM (ModulesT AS M RIGHT JOIN SkillT AS S ON M.SkillID = S.SkillID) LEFT JOIN EmployeeT AS E ON M.EENumber = E.EENumber
GROUP BY E.EENumber, E.FirstName, E.LastName, S.Description, M.Mod_Inactive
HAVING (((M.Mod_Inactive)=False))
ORDER BY E.EENumber, S.Description

Any help would be much appreciated

[CJ_London]

I think you may have your joins mixed up

First you need to get a list of all employees and all possible skills. To do this you use what is called a Cartesian query (no joins)

SELECT EENumber, FirstName, LastName, SKillID, Description
FROM EmployeeT, SkillT

then you want to compare this list with what is in the modules table - and report back where there is not a matching record, for this you need a left join from the above query to the modulesT table and find those records where the module fields are null

SELECT T.*
FROM (SELECT EENumber, FirstName, LastName, SKillID, Description FROM EmployeeT, SkillT) T LEFT JOIN ModulesT M ON T.EENumber=M.EENumber AND T.SkillID=M.SkillID
WHERE M.SkillID is Null
ORDER BY EENumber, Description

I'm not sure of the meaning of Mod_Inactive - but the above does not take it into account. If the employee has not had the training, then there won't be a related record with Mod_Inactive set either way.

[londo]

Thanks that worked great now how would i filter the list further i want to filter from the modulesT Mod.Inactive is a yes/no box

As when an employee is trained it gets added to the modulesT but when the record expires the Mod.Inactive becomes true so is required retraining in that skill set i know i could delete the record and that would be correct but if i mark the record as inactive i can`t work out how to filter that in the main query

so i would have something like this

EENumber FirstName LastName Description ModInactive
1             Joe           Blogs       Skill 1       No (False)          
1             Joe           Blogs       Skill 2       Yes(True)
1             Joe           Blogs       Skill 3       No (False)
2             Paul          Blank       Skill 1       No (False)
2             Paul          Blank       Skill 1       Yes(True)

so in the above Joe has been trained in skill 2 but has expired so skill 2 should show as not trained.
Paul Blank has been trained in skill 1 and retrained in skill 1 so should not show in the list

Thanks
Londo...

[CJ_London]

extend the criteria

WHERE M.SkillID is Null OR M.Mod_Inactive=True

[londo]

extend the criteria

WHERE M.SkillID is Null OR M.Mod_Inactive=True

Fantastic been stuck on this for weeks so simple i will study this now here is my final working query

SELECT T.EENumber, T.FirstName, T.LastName, T.Description
FROM (SELECT EENumber, FirstName, LastName, SKillID, Description FROM EmployeeT, SkillT)  AS T LEFT JOIN ModulesT AS M ON (T.SkillID = M.SkillID) AND (T.EENumber = M.EENumber)
WHERE M.SkillID Is Null OR M.Mod_Inactive=True
ORDER BY T.EENumber, T.Description

thank you so much really appreciate the help do you have any reccomedations of any good books for the querys i really need to get my head round the subquery and the joins

[CJ_London]

no books as such - just browse the forums or try a few of the online lessons.

things to bear in mind is data needs to be normalised to make it easy to manipulate. This means good table and relationships design. And also, particularly when dealing with large datasets (100k+ records), is to ensure your tables are properly indexed.