From: Luca Ferrari <fluca1978@infinito.it>
To: pgsql-sql@postgresql.org
Subject: sql problem with join
Date: Wed, 15 Nov 2006 09:32:04 +0100
User-Agent: KMail/1.9.5
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200611150932.04806.fluca1978@infinito.it>

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