From: Aaron Bono <postgresql@aranya.com>
To: Luca Ferrari <fluca1978@infinito.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: sql problem with join
Date: Fri, 17 Nov 2006 17:04:28 -0600
Message-ID: <bf05e51c0611171504q2e60151o2f9a4a80081e4aff@mail.gmail.com> (raw)
In-Reply-To: <200611151759.58103.fluca1978@infinito.it>
References: <30186.45923.qm@web31807.mail.mud.yahoo.com>
<200611151759.58103.fluca1978@infinito.it>
On 11/15/06, Luca Ferrari <fluca1978@infinito.it> wrote:
>
> 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?
This should list out all roles and their required skills for each person.
It will show you the desired level and the evaluated level.
select
p.*,
r.*,
rs.desired_level,
coalesce(ps.evaluated_level, 0) as evaluated_level
from people p
inner join peopleRole pr on (p.id_person = pr.id_person)
inner join role r on (pr.id_role = r.id_role)
inner join roleSkill rs on (r.id_role = rs.id_role)
inner join skill s on (rs.id_skill = s.id_skill)
left outer join peopleSkill ps on (
p.id_person = ps.id_person
and s.id_skill = ps.id_skill
)
You will have to check it for syntax problems since I didn't run it.
-Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.comhttp://codeelixir.com
==================================================================
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: postgresql@aranya.com, fluca1978@infinito.it
Subject: Re: sql problem with join
In-Reply-To: <bf05e51c0611171504q2e60151o2f9a4a80081e4aff@mail.gmail.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