Group,

I need to write a SQL which find all the match recording for a
particular person

For example
COL1               COL2
john                 a
john                 b
john                 c
joe                  a
joe                  c
tim                  a
tim                  b
tim                  c

I would like to find all the person who has the same entry as john (a,
b,c ),  which tim is.

How should i build that SQL?

Thanks for your help

On 29.06.2011 17:08, charles wrote:

It seems you do not read replies you get.  And why do you open a new thread?

Cheers

        robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On 6/30/2011 6:41 AM, Preston wrote:

I almost didn't reply to this since it appears to be a homework problem,
but the simplest way to solve it is to use the following PL/SQL function
and compare the concatenated 2nd column values.

CREATE OR REPLACE FUNCTION Concatenate_List (p_cursor IN  SYS_REFCURSOR,
delimiter IN VARCHAR2)
  RETURN  CLOB
IS
  l_return  VARCHAR2(32767);
  l_temp    VARCHAR2(32767);
BEGIN
  /* This function executes the passed query and creates a string of all the
  returned values, separated by the passed delimiter.
  This is one solution of many to allow one to aggregate multiple values
  from a correlated subquery into a string.  See here for more info:
  http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
   */
  LOOP
    FETCH p_cursor
    INTO  l_temp;
    EXIT WHEN p_cursor%NOTFOUND;
    IF l_return IS NOT NULL THEN
        l_return := l_return || delimiter;
    END IF;
    l_return := l_return || l_temp;
  END LOOP;
  CLOSE p_cursor;
  RETURN l_return;
END;
/

//Walt

Thanks Preston for explain to me.  I did not see my postings, thought
my messages did not go through.

On Jul 1, 6:17 pm, charles <dshprope...@gmail.com> wrote:

Hello There,

See if this helps.

Select t2.Col1  from tmp_data t1
Inner Join tmp_data t2 on t1.Col2=t2.Col2
And t1.Col1='john' and t2.Col1!='john'
group by t2.Col1
having COUNT(distinct t2.Col2) =
(
Select count(distinct Col2) from tmp_data Where Col1='john'
)

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com