Help with comparing two arrays to create a third array.

[tnhillbilly]

Here are the two arrays to compare.

 

Peopleskills

PeopleID SkillID

2     2

2     7

2     9

2           11

3     2

3                  12

3     14

4     5

 

Equipskills

EquipID SkillID

1             2      

1             9

1             11

2             5

2             7

2             9

2             12

2             13

2             14

3             2

4             11

 

The common link is SkillID.

Each EquipID has some required SkillIDs that people must have to operate that machine.

As long as a PeopleID has the required SkillIDs from the EquipSkills, that person has permission to that machine.

This will be used create a third array.

It will look something like the following.

 

                          EquipIDs

                      1    2    3    4    5    6    7    8      9      10      11    12

PeopleIDs 

2                    x    x

3                          x

4

5

 

What might be the best way to code this?

 

 

 

[btherl]

So you want to know which people have all skills required for an equipment item?  You could start with a left join from equipment to people:

 

SELECT EquipID, PeopleID
FROM Equipskills e
LEFT JOIN Peopleskills p ON (p.SkillID = e.SkillID)

 

If a person is missing a skill for an equipment, there will be a row for that equipment/person combination with "p.SkillID is null".  If the person has all skills, there will be no row with "p.SkillID is null" for that equipment/person combination.

 

So to further reduce this to just "people with the required skills":

 

SELECT EquipID, PeopleID
FROM (the query above) AS subq
GROUP BY EquipID, PeopleID
HAVING MIN(CASE WHEN PeopleID IS NULL THEN 0 ELSE 1 END) = 1

 

There's no aggregator which will directly tell you if there's a null present, so i'm converting the nulls into 0s and the non-nulls into 1s, then finding the minimum.  If any nulls are present the minimum is 0 (and the person is not skilled for that equipment), otherwise it's 1 and the person is skilled.

 

If you try this query and it doesn't work please show me the error message (or the result set if the result set is incorrect).

[tnhillbilly]

thanks,

 

from the array data, PeopleID 2 is authorized to run EquipID 1 but not 2 because their skills don't match all the required skills for EquipID 2.

[tnhillbilly]

Thanks btherl for the boost.

I was banging my head on this all week.

 

 

Here's what i ended up doing:

 

I ran this query for each person and machine.

If the "Result" column was zero, then that person was authorized to run that machine.

 

SELECT EquipID, PeopleID, count(PeopleID), Count(e.SkillID), (count(PeopleID) - Count(e.SkillID)) as Result FROM Equipskills e left join (Select PeopleID, SkillID from Peopleskills  where PeopleID = ".$allpeople[$a]['ID']." ) p ON (p.SkillID = e.SkillID) where EquipID = ".$allequip[$b]['ID']." Order by EquipID, e.SkillID

 

If the "Result" of the query was zero, i wrote a "1" to an array indicating so, then i was able to display the data as a matrix.

 

[btherl]

Congratulations, that's a pretty tricky task to do in SQL!  So you're taking advantage of that count(PeopleID) will always be the total skill count, but count(e.SkillID) will skip null values, so it'll be the total count of skills matched by that person.  If the counts match, then that person has all required skills.

[tnhillbilly]

here's the result.

 

thanks again.

 

 

[attachment deleted by admin]