Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL - filtering data based on AND condition

Re: SQL - filtering data based on AND condition

From: gospar <no.spam_at_my.place>
Date: Wed, 4 Jun 2003 16:05:07 +0200
Message-ID: <bbku7s$juq$1@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 Wed Jun 04 2003 - 09:05:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US