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';
