From: Richard Broersma Jr <rabroersma@yahoo.com>
To: Luca Ferrari <fluca1978@infinito.it>
To: pgsql-sql@postgresql.org
Subject: Re: sql problem with join
Date: Wed, 15 Nov 2006 07:20:45 -0800 (PST)
Message-ID: <30186.45923.qm@web31807.mail.mud.yahoo.com> (raw)
In-Reply-To: <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?
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.
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: rabroersma@yahoo.com, fluca1978@infinito.it
Subject: Re: sql problem with join
In-Reply-To: <30186.45923.qm@web31807.mail.mud.yahoo.com>
* 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