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
Message-ID: <200611150932.04806.fluca1978@infinito.it> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fluca1978@infinito.it
Subject: Re: sql problem with join
In-Reply-To: <200611150932.04806.fluca1978@infinito.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox