Try either a union, a sub-query, or a 3 way join where the join is based on
the language spoken. If you can't work it out let me know.
"gospar" <no.spam_at_my.place> wrote in message
news:bbku7s$juq$1_at_sunce.iskon.hr...
> Maybe you didn't understand the problem, but a simple query (if you tought
> so) won't solve it.
> The following IS NOT a solution for example 1 (Give me all persons who
speak
> English AND German):
>
> SELECT p.PERSON_ID, p.NAME
> FROM LANGUAGE_SKILL l, PERSON p
> WHERE p.PERSON_ID=l.PERSON_ID AND l.LANG_ID=1 AND l.LANG_ID=2;
>
> Maybe I'm really missing something, but I think that is not the case.
>
> Bye, gospar
>
> "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message
> news:WPmDa.828793$OV.785901_at_rwcrnsc54...
> > Looks like homework. What class is this for and what school?
> > Jim
> >
> > --
> > Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> > with family. Remove the negative part, keep the minus sign. You can
> figure
> > it out.
> > "gospar" <no.spam_at_my.place> wrote in message
> > news:bbkrbp$hd9$1_at_sunce.iskon.hr...
> > > I wonder if this problem can be solved with one query statement:
> > >
> > > CREATE TABLE PERSON(PERSON_ID NUMBER(10),NAME VARCHAR2(50));
> > > INSERT INTO PERSON VALUES(1,'Mike');
> > > INSERT INTO PERSON VALUES(2,'John');
> > > CREATE TABLE LANGUAGE(LANG_ID NUMBER(10),NAME VARCHAR2(50));
> > > INSERT INTO LANGUAGE VALUES(1,'English');
> > > INSERT INTO LANGUAGE VALUES(2,'German');
> > > INSERT INTO LANGUAGE VALUES(3,'Italian');
> > > CREATE TABLE LANGUAGE_SKILL(PERSON_ID NUMBER(10), LANG_ID NUMBER(10));
> > > INSERT INTO LANGUAGE_SKILL VALUES(1,1); -- Mike speaks English
> > > INSERT INTO LANGUAGE_SKILL VALUES(1,2); -- Mike speaks German
> > > INSERT INTO LANGUAGE_SKILL VALUES(2,1); -- John speaks English
> > > COMMIT;
> > >
> > > Question:
> > > How to select all persons who speak two (or any number) of selected
> > > languages.
> > >
> > > Example 1: Give me all persons who speak English AND German.
> > > Result 1: Mike
> > >
> > > Example 2: Give me all persons who speak English AND English
> > > Result 2: Mike, John
> > >
> > > Example 3: Give me all persons who speak English AND Italian AND
German
> > > Result 3: none
> > >
> > > Note:
> > > Language and Person table can have any number of rows.
> > > There should be 3 SELECT statements for this 3 examples which differ
> only
> > in
> > > WHERE clause.
> > >
> > >
> >
> >
>
>
Received on Thu Jun 12 2003 - 01:56:35 CDT
