pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsql problem with join
4+ messages / 3 participants
[nested] [flat]
* sql problem with join
@ 2006-11-15 08:32 Luca Ferrari <fluca1978@infinito.it>
2006-11-15 15:20 ` Re: sql problem with join Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Luca Ferrari @ 2006-11-15 08:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sql problem with join
2006-11-15 08:32 sql problem with join Luca Ferrari <fluca1978@infinito.it>
@ 2006-11-15 15:20 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-11-15 16:59 ` Re: sql problem with join Luca Ferrari <fluca1978@infinito.it>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Broersma Jr @ 2006-11-15 15:20 UTC (permalink / raw)
To: Luca Ferrari <fluca1978@infinito.it>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sql problem with join
2006-11-15 08:32 sql problem with join Luca Ferrari <fluca1978@infinito.it>
2006-11-15 15:20 ` Re: sql problem with join Richard Broersma Jr <rabroersma@yahoo.com>
@ 2006-11-15 16:59 ` Luca Ferrari <fluca1978@infinito.it>
2006-11-17 23:04 ` Re: sql problem with join Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 4+ messages in thread
From: Luca Ferrari @ 2006-11-15 16:59 UTC (permalink / raw)
To: pgsql-sql
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?
Thanks,
Luca
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sql problem with join
2006-11-15 08:32 sql problem with join Luca Ferrari <fluca1978@infinito.it>
2006-11-15 15:20 ` Re: sql problem with join Richard Broersma Jr <rabroersma@yahoo.com>
2006-11-15 16:59 ` Re: sql problem with join Luca Ferrari <fluca1978@infinito.it>
@ 2006-11-17 23:04 ` Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 4+ messages in thread
From: Aaron Bono @ 2006-11-17 23:04 UTC (permalink / raw)
To: Luca Ferrari <fluca1978@infinito.it>; +Cc: pgsql-sql
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
==================================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-11-17 23:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-15 08:32 sql problem with join Luca Ferrari <fluca1978@infinito.it>
2006-11-15 15:20 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-11-15 16:59 ` Luca Ferrari <fluca1978@infinito.it>
2006-11-17 23:04 ` Aaron Bono <postgresql@aranya.com>
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