Re: Query - student, skill
| От | Jayadevan M |
|---|---|
| Тема | Re: Query - student, skill |
| Дата | |
| Msg-id | CAFS1N4hDoyf4Gd6Cvp1zRpEMWpP_6vTuwBwZyH_+udygg0NUhg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Query - student, skill (Kevin Grittner <kgrittn@ymail.com>) |
| Ответы |
Re: Query - student, skill
|
| Список | pgsql-novice |
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.I got another way of doing this also.....
On Wed, Oct 9, 2013 at 8:12 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
Jayadevan M <maymala.jayadevan@gmail.com> wrote:> select s.id, s.name
> To fetch records of students who know , say, 'Java' and 'Oracle',
> is this the best way?
>> from stud s> where sk_name = 'Oracle'
> 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
> ;
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
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
В списке pgsql-novice по дате отправления: