0

I have a table. Let say it has 2 columns . Column 1 is Id , column 2 is dependent ID If I want to know , all Ids that are dependent upon an ID. This dependency can be transitive .

i.e if Id1 is dependent upon Id2 and Id3 is dependent upon Id1. then If I want all dependents upon ID2 tesult should then the both Id2 and Id1.

For this I will have to fire multiple queries to mysql until I get a nullSet.

I.e

Select Id where dependentID='ID2';

This will give one set. Then I will have to recursively fire the above query on the ID set which is outputted by the above query. Can I do it in just one query somehow i.e only one I/O or is there a better way then the above approach ?

The database I am using is MYSQL.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
Peter
  • 2,719
  • 4
  • 25
  • 55
  • Take a look at questions http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql and http://stackoverflow.com/questions/20215744/mysql-hierarchical-recursive-query. Perhaps there might some discussions there that may be helpful. Blog post http://guilhembichot.blogspot.com/2013/11/with-recursive-and-mysql.html might be helpful also – zedfoxus Mar 07 '15 at 19:49

1 Answers1

0

Life will be simpler if you store the pairs in a conical order, such as 

INSERT INTO tbl (id1, id2) VALUES
(LEAST($a, $b), GREATEST($a, $b));

Back to your "recursive" query. No, there is no way to do it inside MySQL's SQL. You must write a loop in your favorite programming language. Well, OK, you could write a MySQL Stored Procedure, that that feels really difficult.

In your language, I would suggest a loop that adds to an array. Keep the array sorted. Each time through the loop, do one more SELECT using an IN clause with the array elements. Terminate the loop when you find no new items for the array.

The SELECT would be something like

( SELECT id2 FROM tbl WHERE id1 IN (...) )
UNION
( SELECT id1 FROM tbl WHERE id2 IN (...) )

It would be good to have compound indexes (id1,id2) and (id2, id1).

Rick James
  • 135,179
  • 13
  • 127
  • 222