Re: sql problem with join

> 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?

May be you could show the results you are getting and then make up some results that you would
really like to get. I am not entirely clear what you are trying to achieve.

Regards,

Richard Broersma Jr.