SQL - filtering data based on AND condition
From: gospar <no.spam_at_my.place>
Date: Wed, 4 Jun 2003 15:15:59 +0200
Message-ID: <bbkrbp$hd9$1_at_sunce.iskon.hr>
[Quoted] [Quoted] I wonder if this problem can be solved with one query statement:
How to select all persons who speak two (or any number) of selected languages.
Date: Wed, 4 Jun 2003 15:15:59 +0200
Message-ID: <bbkrbp$hd9$1_at_sunce.iskon.hr>
[Quoted] [Quoted] 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 EnglishINSERT 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 Wed Jun 04 2003 - 15:15:59 CEST