SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to select all X.ID where X.y in {list of y}}

Michael WeinbergerMay 8 2012 — edited May 8 2012

Hi !

Could anyone please help me with my sql-problem? It seemed simple at the beginning, but now I am totally stuck. Maybe this is something most of you have encountered already.

I have trainer. These trainer have skills. The user selects a list of skills. And would like to see the trainers posessing all of them.

T_trainer
trainer - skill
Adam - 1
Adam - 2
Eva - 2
Eva - 3

User's selection: 1,2 should return Adam

but my sql:

select trainer_id from T_trainer where skill in
(select column_value as skill from table(string2table('1:2')));

returns Adam, Eva

There are similar problems with MINUS

select column_value as skill, trainer_id from TIS_TRAINER_BEREICHE_ZO, table(string2table(:P1_TB))
MINUS
select to_char(BEREICHE_ID) as skill, trainer_id from TIS_TRAINER_BEREICHE_ZO;

How to make it right?

Thank you very much,
Michael

This post has been answered by Himanshu Binjola on May 8 2012

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 5 2012

Added on May 8 2012

#apex, #group, #in, #sql

8 comments

665 views