Re: ORACLE 8i Newbie DISTINCT and EXISTS problem

You need to qualify the subquery:
First:

Select distinct (a.begval) from table_1 a where exists (select distinct (b.begval) from table_2 b where a.begval = b.begval);

Even with this, if ANY b.begval rows meet the where clause condition ALL distinct a.begvals will be returned since EXISTS actually means if one or more meet the test then return TRUE ..

If you only want the a.begval values that MATCH the b.begval values you need:

Select distinct (a.begval) from table_1 a where a.begval IN (select distinct (b.begval) from table_2 b);

hth,

hampsonpaul80_at_hotmail.com (stimpy) wrote:

>Hi
>I'm a newbie and I am trying to get the common records between two
>large tables
>using the following query:
>
>Select distinct (a.begval) from table_1 a
>where exists (select distinct (b.begval) from table_2 b);
>
>I know table_1.begval has 15 distinct values and table.begval has only
>2 distinct values (a subset of table_1).
>
>What I can't understand is why the query returns all 15 distinct value
>form table_1 rather than the 2 values shared by both tables.Is it
>because it involves DISTINCT or is it because of the EXIST?
>
>I know there are other ways to get this information (INTERSECT, IN
>etc.), what I can't understand is why this doesn't work.
>
>Thanks in advance
>
>Paul

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Thu Sep 26 2002 - 12:32:29 CDT