Re: Query - student, skill

Thanks. Since the number of conditions and the type of conditions (AND, OR
) etc are not known beforehand, I felt that generating the query as above
(aliasing) may be a bit more difficult compared to generating a list of
similar INTERSECTs or UNIONs. Since we have equijoins and and an exact
match on skill, it should get executed fast?
I got another way of doing this also.....
WHERE SKILL IN ('JAVA','ORACLE') group by student_id having count(*) = 2.
If there are 3 skills, do a count(*)=3.
By the way, combination of student_id/skill will be unique in that table.
Any other ideas? I am collecting options - will check performance and use
the best.

On Wed, Oct 9, 2013 at 8:12 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)com> wrote:
>
> > To fetch records of students who know , say, 'Java' and 'Oracle',
> > is this the best way?
> >
> > 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 in most (maybe all?) cases the set operations like
> INTERSECT cause the queries on both sides to be executed and the
> set operation performed on the results. It should be faster just
> to use two joins to the skill table:
>
> select s.id, s.name
> from stud s
> join stud_skill s_k on s.id = s_k.stud_id
> join skill sk1 on sk1.id = s_k.skill_id and sk1.sk_name = 'Java'
> join skill sk2 on sk2.id = s_k.skill_id and sk2.sk_name = 'Oracle'
> ;
>
> If the skill names are not unique, you might want to throw a
> DISTINCT in there, too.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>