Raghuraman_New
New User
Joined: 03 Oct 2008 Posts: 43 Location: Chennai,India.
|
|
|
|
Hi ,
My requirement is to fetch the rows(data) from the Table having simillar Patters of data's in the same table itself.
Example : I am having the table with 2 coloumn C1 and C2,
Code: |
----------------------
C1 C2
----------------------
U1 A
U1 B
U1 C
U2 E
U2 F
U3 A
U3 B
U3 C
U3 D
U4 A
U4 B
U4 C
U5 E
U5 F
U6 D
U6 E
U7 A
U7 B
U7 C
|
Required Output :
Code: |
-------------------------
C1 C2
-----------------------
U1 U4
U1 U7
U2 U5
U4 U1
U5 U2
U7 U1
|
Query Rule :
The U1,U4,U7 are having the same kind of C2 values (A,B,C) and
U2, U5 having the same kind of C2 values (E,F).
Important : U1 and U3 having (A,B,C) as common C2 values but U3 has one more C2 value D. These Combinations are not valid . I need the exact matching one.
Note :
I have tried for single input case and got output but i want for general case like above. Please let me know if you have any questions or concerns.
Our Shop is having DB2 z/OS V8 only. |
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Code: |
Select a.C1 , b.c1 from TAB1 a , TAB1 b
where a.c2 = b.c2 -- SELF JOIN
and a.c1 <> b.c1 -- make sure we don't compare U1 with U1
and a.c1 = (select min(a2.c1) from TAB1 a2 where a2.c2 = a.c2) -- to eliminate output U4=U1, U4=U7 when u1=u4 u1=u7
and a.c2 = (select min(a2.c2) from TAB1 a2 where a2.c1 = a.c1) -- to avoid checking U1 3 times (for each value of c2)
and not exists (
select c2 from TAB1 C
where c.c1 = a.c1 -- All C2 for U1
and not exists ( -- Can't find
select c2 from TAB1 d where d.c1 = b.c1 and d.c2 = c.c2) -- the same C2 in U4
)
and not exists (
select c2 from TAB1 C
where c.c1 = b.c1 -- All C2 for U4
and not exists ( -- Can't find
select c2 from TAB1 d where d.c1 = a.c1 and d.c2 = c.c2) -- the same C2 in U1
) |
|
|