Re: Query - student, skill

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