help with query

[avisam]

Hello,

I have one table with certification data:
cer ID, cer sub 3, cer sub 2, cer sub 1, cer name

for the combination cer sub 3 & cer sub 2 & cer sub 1 there are multiple certifications names for example:
cer sub 3, cer sub 2, cer sub 1, certification name 1
cer sub 3, cer sub 2, cer sub 1, certification name 2
cer sub 3, cer sub 2, cer sub 1, certification name 3

I have second table with employees certifications:
emp Id, cer ID, cer status (enrolled, completed)

I wish to get a list of all employees and their missing registrations.
for each employee that start but did not completed all the certifications for sub 3 & cer sub 2 & cer sub 1 combination include the missing certifications id.

for example:

employees table:
emp1
emp2
emp3

certification table:
a,b,c,name 1
a,b,c name 2

employees certifications:
emp1, a,b,c,name 1,completed
emp1,a,b,c,name 2,completed
emp2,a,b,c,name 1,completed

query result:
emp2,a,b,c,name 2

Thank you for your help

 

[avisam]

Hello,

I think it is more complected.

I need to list for each employee -
If he start a certification (cer sub 1 & 2 & 3 combination) but did not finished all cer name under it, list all registartions for cer sub 1,2,3 combination and add missing registrations.

I know how to it using code:
1. Based on certification table I will create a dictionary:
key: CerSub1 & CerSub2 & CerSub1
value: certifications ID's separated by comma like: (1,2,3)
2. Based on Employees certifications table:
Generate list of employees id's.
For each employee:
Go over employees certifications table and generate a dictionary based on employee's certifications records:
Key: CerSub1 & CerSub2 & CerSub1
value: certifications ID's separated by comma like: (1,2,3)
3. Go over employee's dictionary:
search each key in certification's dictionary.
If key's values are equal then the employee finished the certification.
for example: (1,2,3) = (1,2,3).
If not - find missing certifications for key & employee and generate records in result table.

Do you have any idea how to implement this by SQL ?

Thank you for your help

 

[JHB]

value: certifications ID's separated by comma like: (1,2,3)

Don't store multiply values/results in a single field, create a (sub) table for each certifications ID value, then you can use the function Count to determine if all 3 CerSub has been done.

 

[avisam]

Hello,

Thank you for your answer. I am looking for SQL solution to my problem.

Thank you for your help