OraFAQ Forum: SQL & PL/SQL » Query to list the person who speaks every language !

Home » SQL & PL/SQL » SQL & PL/SQL » Query to list the person who speaks every language !

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query to list the person who speaks every language ! [message #21040]

Mon, 08 July 2002 14:59

lujogre
Messages: 1
Registered: July 2002

Junior Member

Folks,

Please consider the following data set:

Table: LANCAP (NAME, LANGUAGE)

NAME LANGUAGE
Brenda French
Brenda English
Brenda Spanish
Joe English
Kent English
Lujack French
Lujack German
Lujack English
Lujack Spanish
Mary German
Mary English
Mary French
Melanie French
Melanie English
Richard German
Richard Spanish
Richard English
Richard French

I must list the person who speaks all of the languages Brenda speaks, for that purpose I wrote the following query:

select t1.name, t1.language
from lancap t1
where exists (select t1.language
from lancap t2
where t2.name = 'Brenda'
and t2.language = t1.language)

However, the query above listed is returning people who speaks at least one of the languages that Brenda speaks. I need to obtain is ONLY the people who SPEAKS ALL OF THE LANGUAGES BRENDA speaks.

Any thoughs? Your feedback would be greatly appreciated.

Thanks !

Report message to a moderator

Re: Query to list the person who speaks every language ! [message #21041 is a reply to message #21040]

Mon, 08 July 2002 16:15

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL> select * from lancap;

NAME       LANGUAGE
---------- ----------
Brenda     French
Brenda     English
Brenda     Spanish
Joe        English
Kent       English
Lujack     French
Lujack     German
Lujack     English
Lujack     Spanish
Mary       German
Mary       English
Mary       French
Melanie    French
Melanie    English
Richard    German
Richard    Spanish
Brenda     German
Richard    Spanish
Richard    Spanish

19 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  select name, cnt from
  2  (select name,(count (distinct(language))) cnt from lancap
  3  group by name)
  4* where cnt=(select count(distinct(language)) from lancap)
SQL> /

NAME              CNT
---------- ----------
Brenda              4
Lujack              4
*************************************************
if you are sure there just 4 languages then u can change line 4 in above code to

where cnt=4;
*************************************************

Report message to a moderator

Re: Query to list the person who speaks every language ! [message #21046 is a reply to message #21040]

Tue, 09 July 2002 05:47

Jon
Messages: 483
Registered: May 2001

Senior Member

Unfortunately, I don't think so...I think this only works because Lujack speaks more languages than Brenda. If not, it returns no rows:

SQL> SELECT t1.NAME, t1.LANGUAGE
2 FROM lancap t1
3 WHERE NOT EXISTS (SELECT *
4 FROM lancap t2
5 WHERE t2.LANGUAGE NOT IN (
6 SELECT t3.LANGUAGE FROM lancap t3
7 WHERE t3.LANGUAGE = t2.LANGUAGE
8 AND t3.name = t1.name));

no rows selected

SQL> select name, language
2 from lancap
3 order by 1, 2;

NAME LANGUAGE
-------------------- --------------------
Brenda English
Brenda French
Brenda Spanish
Joe English
Kent English
Lujack English
Lujack French
Lujack Spanish
Mary English
Mary French
Mary German
Melanie English
Melanie French
Richard English
Richard German
Richard Spanish

16 rows selected.

Report message to a moderator

Re: Query to list the person who speaks every language ! [message #21049 is a reply to message #21040]

Tue, 09 July 2002 06:13

AYSEGUL
Messages: 4
Registered: July 2002

Junior Member

select name from lancap
where language= 'french'
and language='english'
and language='spanish';

Report message to a moderator

Re: Query to list the person who speaks every language ! [message #21077 is a reply to message #21040]

Wed, 10 July 2002 19:19

Su
Messages: 154
Registered: April 2002

Senior Member

Check this out.

SQL> select * from myp_language;

NAME LANGUAGE
-------------------- ------------
Brenda French
Brenda English
Brenda Spanish
Joe English
Kent English
Lujack French
Lujack German
Lujack English
Lujack Spanish
Mary German
Mary English
Mary French
Melanie French
Melanie English
Richard German
Richard Spanish
Richard English
Richard French

SQL> edit
Wrote file afiedt.buf

1 select distinct name from myp_language a where not exists (
2 select language from myp_language where name = 'Brenda'
3 minus
4* select language from myp_language where name = a.name)
SQL> /

NAME
--------------------
Brenda
Lujack
Richard

SQL>

It lists out the names of who speak what Brenda speaks (and may be also be other languages). But if you want to list the names who only speaks what exactly Brenda speaks, check the following.

SQL> delete from myp_language where name='Richard' and language='German';

1 row deleted.

SQL> select * from myp_language;

NAME LANGUAGE
-------------------- ------------
Brenda French
Brenda English
Brenda Spanish
Joe English
Kent English
Lujack French
Lujack German
Lujack English
Lujack Spanish
Mary German
Mary English
Mary French
Melanie French
Melanie English
Richard Spanish
Richard English
Richard French

17 rows selected.

SQL> ed
Wrote file afiedt.buf

1 select distinct name from myp_language a where not exists (
2 (select language from myp_language where name = 'Brenda'
3 minus
4 select language from myp_language where name = a.name))
5 intersect
6 (select distinct name from myp_language a where not exists (
7 select language from myp_language where name = a.name
8 minus
9* select language from myp_language where name = 'Brenda'))
SQL> /

NAME
--------------------
Brenda
Richard

The second one lists only the names who speak same languages and nothing else.

Good luck :)

Report message to a moderator

Previous Topic:	Julian Date
Next Topic:	how to give sequence number to my table group by id?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 15:28:37 CDT 2024