sql problem with join

From: Luca Ferrari <fluca1978(at)infinito(dot)it>
To: pgsql-sql(at)postgresql(dot)org
Subject: sql problem with join
Date: 2006-11-15 08:32:04
Message-ID: 200611150932.04806.fluca1978@infinito.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Broersma Jr 2006-11-15 15:20:45 Re: sql problem with join
Previous Message Bruno Wolff III 2006-11-15 06:50:48 Re: SQL - update table problem...