Hi all,
I've got a problem tryng to define a view with a few joins, I'll appreciate if
someone could drive me in the definition of such query.
I've got a table roleSkill that contains a row for each skill belonging to a
defined role and with the desired competence level for such skill in such
role:
roleSkill = (id_role, id_skill, desired_level) PRIMARY KEY(id_role,id_skill)
Then I've got a table peopleSkill with a row for each evaluated skill for a
person (a skill in this case could not belong to a defined role):
peopleSkill = (id_person,id_skill, evaluated_level) PRIMARY
KEY(id_person,id_skill)
Finally I've got an association between a person and a role:
peopleRole = (id_person,id_role) PRIMARY KEY(id_person,id_role)
Now I'd like to build a view that shows a row for each skill a person has
(i.e. it has been evaluated) and should have (i.e. it is listed in the role).
Initially I tried with something like:
select p.*,r.*
from roleSkill r
JOIN peopleRole p on p.id_role=r.id_role /* this gives me all the roles a
person has and all her skills */
LEFT JOIN peopleSkill s on s.id_skill = r.id_skill /* this should keep all
the role skills and show the one evaluated */
So the first join should give me all the role played from a person, with its
skills, the second join should take the evaluated skills and should keep the
not evaluated (i.e., present only in roleSkill) rows. But this is not
working, I see a lot of rows with the same role for the same person and I
cannot find the error.
Any clues?
Thanks,
Luca
