Database Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print All Forums Database BPP 24 posts Joined 01/10 06 Mar 2014 finding links between two col values.. from below data set   Cola Colb 1 20 20 31 31 40 40 5   10 11 10 120 120 13   100 201 201 210 201 103   1000 465   I would like know that values 1,20,31,40 and 5 are related.  100,201,210 and 103 are related and so on.  How do I do this in sql?? thanks in advacne.. BPP Raja_KT 1246 posts Joined 07/09 07 Mar 2014 Will this be ok for you? select a.* from test_table1 a, test_table1 b where a.cola=b.colb   Raja K Thaw My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1 Street Children suffer not by their fault. We can help them if we want. Santanu84 122 posts Joined 04/13 07 Mar 2014 Hi In my understanding you trying to find the parent-child relation. Look for Recursive Query (using Level in Root). Thanking You Santanu BPP 24 posts Joined 01/10 07 Mar 2014 Santanu84: thought about it but dont know where I am going with it..  If someone can play with this code and try it out that would be very helpful...   CREATE VOLATILE TABLE val (col1 INTEGER, col2 INTEGER ) PRIMARY INDEX(col1, col2) ON COMMIT PRESERVE ROWS   DEL FROM val; insert into val( 1 , 20 ); insert into val( 20 , 31 ); insert into val( 31 , 40 ); insert into val( 40 , 5 );   insert into val( 10 , 11 ); insert into val( 10 , 120 ); insert into val( 120 , 13 );   insert into val( 100 , 201 ); insert into val( 201 , 210 ); insert into val( 201 , 103 );   insert into val( 1000 , 465 );     WITH RECURSIVE match(col1,col2,levl) AS ( SELECT col1,col2,0 lvl FROM val UNION ALL  SELECT match.col1, val.col2, match.levl+1 FROM val, match WHERE match.col1=val.col2 AND match.levl<10 )  SELECT DISTINCT col1, col2 FROM match ORDER BY 1,2;         BPP dnoeth 4628 posts Joined 11/04 07 Mar 2014 You just have to find the root level, then i's easy :-) WITH RECURSIVE match(grp, col1, col2, levl) AS ( SELECT col1, col1, col2, 0 AS levl FROM val AS t1 WHERE NOT EXISTS ( SELECT * FROM val AS t2 WHERE t2.col2 = t1.col1 ) UNION ALL SELECT match.grp, val.col1, val.col2, levl + 1 FROM val JOIN match ON match.col2 = val.col1 WHERE levl <= 10 ) SELECT * FROM match ORDER BY 1,2; Hopefully there are no circular references in the data, better keep the filter on levl. Dieter BPP 24 posts Joined 01/10 07 Mar 2014 thank you Dieter. grouping them is key for me and you made it look so simple. I am eliminating circular references, I do not need them. This is one of the many knots I got to resolve.. thanks again!!   Thanks Raja and Satanu_84.         BPP BPP 24 posts Joined 01/10 08 Mar 2014 Dieter, may be I did not understand when you said circular reference and may be it found me :-((   here is real dataset..  we got case 1) a= b and b = a and then  case 2) we got a = b, a= c , a=d , e=c, c=e. in correlated query changed to exists then it is kind of working but grouping for each value.  Not exists is not workig as rows are filtered out.   Any more thoughts?  thank you..  Insert into val( 531,691 , 1,997,292 );  insert into val( 531691 , 1997292 );   insert into val( 1515094 , 1515093 );   insert into val( 1515094 , 5122156 );   insert into val( 1515094 , 5122155 );   insert into val( 1997292 , 5518168 );   insert into val( 1997292 , 531691 );   insert into val( 5518168 , 1997292 );      INSERT INTO val( 301 , 500 ); INSERT INTO val( 500 , 301 ); INSERT INTO val( 500 , 401 ); INSERT INTO val( 401 , 500 ); INSERT INTO val( 600 , 700 ); INSERT INTO val( 700 , 600 ); INSERT INTO val( 8000 , 700 ); INSERT INTO val( 700 , 8000 );   BPP dnoeth 4628 posts Joined 11/04 09 Mar 2014 If there are cycles you need to detect them, the only way is to built a materialized path and check if the current value is already included. And if there's no way to find out the actual start values you must do all possibles paths and then find the minimum value: WITH RECURSIVE match(grp, col1, col2, levl, PATH) AS ( SELECT t1.col1, t1.col1, t1.col2, 0 AS levl, '.' || CAST(t1.col1 AS VARCHAR(501)) || '.' AS PATH FROM val AS t1 UNION ALL SELECT match.grp, val.col1, val.col2, levl + 1, PATH ||TRIM(val.col1) || '.' AS p FROM val JOIN match ON match.col2 = val.col1 AND levl < 50 WHERE match.PATH NOT LIKE '%.' || TRIM(val.col1) || '.%' ) SELECT MIN(grp) AS grp, col1,col2, MAX(levl) FROM match GROUP BY col1,col2 ORDER BY 1,2; Caution, CAST(t1.col1 AS VARCHAR(500)) should be large enough to hold the maximum path length, e.g. if there's a maximum of 50 values in a path and the maximum lengh of a value is 9 you need ((9+1) * 50)+1 = 501 characters and you must check for levl < 50. Depending on the number of rows in the table and the maximum number of rows in a path this might result in a huge spool. In that case rewriting the recursion into a loop in a Stored Procedure might be helpful (you can filter after every loop) . Dieter BPP 24 posts Joined 01/10 10 Mar 2014 Thanks Dieter.  Looks like it is grouping them correctly, I will have to test it out with our data.   Thanks for giving the directions If rows or its size would be a problem, I can try it out with a proc.  thanks once again.. you are simply briliant! :-)).   BPP You must sign in to leave a comment. Active Posters