Query - student, skill

From: Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Query - student, skill
Date: 2013-10-09 08:46:28
Message-ID: CAFS1N4jgevj-B764m6KqVyfNRt01Fi+wq6D139kCzLNK7HfreQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Michael Swierczek 2013-10-09 12:36:53 Re: Query - student, skill
Previous Message James David Smith 2013-10-02 10:02:43 Advice on upgrading from PostgreSQL 9.0