Re: SQL - filtering data based on AND condition
From: Jim Kennedy <kennedy-down_with_spammers_at_attbi.com>
Date: Wed, 04 Jun 2003 13:51:50 GMT
Message-ID: <WPmDa.828793$OV.785901_at_rwcrnsc54>
Date: Wed, 04 Jun 2003 13:51:50 GMT
Message-ID: <WPmDa.828793$OV.785901_at_rwcrnsc54>
[Quoted] 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. [Quoted] "gospar" <no.spam_at_my.place> wrote in message news:bbkrbp$hd9$1_at_sunce.iskon.hr...Received on Wed Jun 04 2003 - 15:51:50 CEST
> 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.
>
>