DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=JbHg0ZIkKWWGP5u2O8Efv06fGHItBAqVpi77fg5p5FOzanb+tctPKHEZks/xuzo1yyE+qWrH2EtfZIsAjUqRlDk2C26W7/N1mKnU7RIbjyu15eVQNwwZ9zelJakfTiE1Tl1VIAdZWxy1V9viP0xRKzOSXEm8Yo601nYt4SVwIgM=
	;
Date: Wed, 15 Nov 2006 07:20:45 -0800 (PST)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: sql problem with join
To: Luca Ferrari <fluca1978@infinito.it>, pgsql-sql@postgresql.org
In-Reply-To: <200611150932.04806.fluca1978@infinito.it>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <30186.45923.qm@web31807.mail.mud.yahoo.com>

> 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.