On Wed, Oct 9, 2013 at 4:46 AM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
> Hi,
> I have three tables - one with student ids and names, second one with skill
> ids and names, third one listing which students have which skills.
> test=# \d stud
> Table "public.stud"
> Column | Type | Modifiers
> --------+------------------------+---------------------------------------------------
> id | integer | not null default
> nextval('stud_id_seq'::regclass)
> name | character varying(100) |
>
> test=# \d stud_skill
> Table "public.stud_skill"
> Column | Type | Modifiers
> ----------+---------+-----------
> stud_id | integer |
> skill_id | integer |
>
> test=# \d skill
> Table "public.skill"
> Column | Type | Modifiers
> ---------+------------------------+----------------------------------------------------
> id | integer | not null default
> nextval('skill_id_seq'::regclass)
> sk_name | character varying(100) |
>
> To fetch records of students who know , say, 'Java' and 'Oracle', is this
> the best way?
> test=# select s.id,s.name from stud s join stud_skill s_k on
> s.id=s_k.stud_id join skill sk on sk.id=s_k.skill_id where sk_name = 'Java'
> intersect select s.id,s.name from stud s join stud_skill s_k on
> s.id=s_k.stud_id join skill sk on sk.id=s_k.skill_id where sk_name =
> 'Oracle';
>
I think that would work. I would do this, though - but I'm not sure
if it's any better:
select s.id, s.name
from
stud s
inner join stud_skill s_kj on s.id = s_kj.id
inner join skill skj on s_kj.skill_id = skj.skill_id
inner join stud_skill s_ko on s.id = s_ko.id
inner join skill sko on s_ko.skill_id = sko.skill_id
where
sko.sk_name = 'Oracle' and skj.sk_name = 'Java'
-Mike