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
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/
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.
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