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

Thanks,
Luca

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

On Wednesday 15 November 2006 16:20 your cat, walking on the keyboard, wrote:
> 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.


Lastly I found the solution, for the moment, that should be the following:
select *
from peopleRole pr
left join roleSkill rs on rs.id_role = pr.id_role
left join peopleSkill ps on ps.id_skill = rs.id_skill

I think it should go, don't you think?

Thanks,
Luca