Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id A4F3E9F9F08
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 17 Nov 2006 19:04:36 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 87901-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 17 Nov 2006 19:04:31 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.185])
	by postgresql.org (Postfix) with ESMTP id 5FAB89F946B
	for <pgsql-sql@postgresql.org>; Fri, 17 Nov 2006 19:04:30 -0400 (AST)
Received: by nf-out-0910.google.com with SMTP id x4so1664227nfb
	for <pgsql-sql@postgresql.org>; Fri, 17 Nov 2006 15:04:28 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
	b=De9z4w9qS5rxC+f7PsSw2liRonqtDh0ZPQjX/HeGsBsQGyCMOG0XY64dI1VgE802eg8CC1e7st00ybZkI2NmtlTw7kt3yYrOp+WvWIbt4TpEapB10QJR23ylwsBQvaaXJPOKlC5ndj2qSYTvrmZCfj+ai0DrgiTxMOp9jGuvvZM=
Received: by 10.78.203.13 with SMTP id a13mr2470056hug.1163804668161;
	Fri, 17 Nov 2006 15:04:28 -0800 (PST)
Received: by 10.78.136.6 with HTTP; Fri, 17 Nov 2006 15:04:28 -0800 (PST)
Message-ID: <bf05e51c0611171504q2e60151o2f9a4a80081e4aff@mail.gmail.com>
Date: Fri, 17 Nov 2006 17:04:28 -0600
From: "Aaron Bono" <postgresql@aranya.com>
To: "Luca Ferrari" <fluca1978@infinito.it>
Subject: Re: sql problem with join
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200611151759.58103.fluca1978@infinito.it>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_10673_9050444.1163804668117"
References: <30186.45923.qm@web31807.mail.mud.yahoo.com>
	<200611151759.58103.fluca1978@infinito.it>
X-Google-Sender-Auth: 473e858489c7a67f
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/188
X-Sequence-Number: 26840

------=_Part_10673_9050444.1163804668117
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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.com
   http://codeelixir.com
==================================================================

------=_Part_10673_9050444.1163804668117
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 11/15/06, <b class="gmail_sendername">Luca Ferrari</b> &lt;<a href="mailto:fluca1978@infinito.it">fluca1978@infinito.it</a>&gt; wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
On Wednesday 15 November 2006 16:20 your cat, walking on the keyboard, wrote:<br>&gt; May be you could show the results you are getting and then make up some<br>&gt; results that you would really like to get.&nbsp;&nbsp;I am not entirely clear what
<br>&gt; you are trying to achieve.<br><br><br>Lastly I found the solution, for the moment, that should be the following:<br>select *<br>from peopleRole pr<br>left join roleSkill rs on rs.id_role = pr.id_role<br>left join peopleSkill ps on 
ps.id_skill = rs.id_skill<br><br>I think it should go, don't you think?</blockquote><div><br>&nbsp;</div>This should list out all roles and their required skills for each person.&nbsp; It will show you the desired level and the evaluated level.
<br><br>select<br>&nbsp;&nbsp;&nbsp; p.*,<br>&nbsp;&nbsp;&nbsp; r.*,<br>&nbsp;&nbsp;&nbsp; rs.desired_level,<br>&nbsp;&nbsp;&nbsp; coalesce(ps.evaluated_level, 0) as evaluated_level<br>from people p<br>inner join peopleRole pr on (p.id_person = pr.id_person)<br>inner join role r on (
pr.id_role = r.id_role)<br>inner join roleSkill rs on (r.id_role = rs.id_role)<br>inner join skill s on (rs.id_skill = s.id_skill)<br>left outer join peopleSkill ps on (<br>&nbsp;&nbsp;&nbsp; p.id_person = ps.id_person<br>&nbsp;&nbsp;&nbsp; and s.id_skill
 = ps.id_skill<br>)<br><br>You will have to check it for syntax problems since I didn't run it.<br></div><br>-Aaron<br clear="all"><br>-- <br>==================================================================<br>&nbsp;&nbsp; Aaron Bono
<br>&nbsp;&nbsp; Aranya Software Technologies, Inc.<br>&nbsp;&nbsp; <a href="http://www.aranya.com">http://www.aranya.com</a><br>&nbsp;&nbsp; <a href="http://codeelixir.com">http://codeelixir.com</a><br>==================================================================

------=_Part_10673_9050444.1163804668117--