1

My application is extremely slow when I move the logic for this to my scripting language. I'm hoping there's a way to leverage MySQL itself for this.

Consider the following table

ID' col1 ' ' col2'
-----------------
1 '   1  ' '  2  '
-----------------
2 '   2  ' '  3  '
-----------------
3 '   3  ' '  4  '
-----------------
4 '   5  ' '  4  '
-----------------

Is there a way to select all records where col1 is 1 or whether col1 is the col2 of a row from a previous match?

For example, if I say I need all records related to 1, I'm looking to get rows 1, 2 and 3.

Here's a query example but it only goes to the first level of depth.

SELECT col2 FROM table WHERE (col1 = 1) OR (col1 IN (SELECT col2 FROM table WHERE (col1 = 1)))
A23
  • 1,596
  • 2
  • 15
  • 31

2 Answers2

1

You will need to create a stored procedure which loops through your tree until no more children will be found. See Recursive mysql select? for how to do this!

In other DBMS you could use WITH RECURSIVE, which is not supported by MySQL, though. Read more about a MySQL-emulation of that here:
http://guilhembichot.blogspot.de/2013/11/with-recursive-and-mysql.html
Basically it is the same thing as before, but with a little more explanation behind it.

There might also be other ways to store your data in the first place to avoid such looping queries:
What is the most efficient/elegant way to parse a flat table into a tree?

Community
  • 1
  • 1
wolfgangwalther
  • 1,226
  • 7
  • 15
  • I was hoping to avoid that but if there's no other choice, I will have to do it. – A23 Oct 31 '14 at 03:18
  • It depends on whether your depth is limited or not. If so, you could just build one query with multiple joins. Some more disuccion on the topic: http://dba.stackexchange.com/questions/46127/recursive-self-joins – wolfgangwalther Oct 31 '14 at 03:29
  • Unfortunately, the depth is not limited. – A23 Oct 31 '14 at 03:47
  • 2
    Well in that case, you have to either change your DBMS, change your table structure or use a looping stored procedure, just as pointed out above. I don't see anything else! – wolfgangwalther Oct 31 '14 at 03:50
  • I ended up using a few business rules and a stored function to achieve this. Thanks for the great links! – A23 Nov 03 '14 at 20:29
0

@a23 The solutions specified above point to a much more robust case handling.

Here is a query which would help in this case.

SELECT * FROM test WHERE col1=1
UNION ALL
SELECT a.* FROM test a, test b WHERE a.col2=b.col1 AND a.col1!=1;

Hope it helps.

web-nomad
  • 6,003
  • 3
  • 34
  • 49
  • This query got me further than any other. Thanks! – A23 Oct 31 '14 at 06:02
  • This query gives you all kind of trees within the table, not just the one starting at `col1=1`. The last element of the tree is not displayed as well, this can be fixed by changing the where clause to `WHERE a.col1=b.col2`. I would still like to know how to go on from here? How do you want to reduce the output to one tree only? – wolfgangwalther Oct 31 '14 at 09:03
  • @wolfgangwalther Are you even sure to have run this query before posting this, because it works well on my mysql? – web-nomad Oct 31 '14 at 09:15
  • I did run that query. But I added a lot more data to the table as well, including totally independent trees, that are not related to the tree starting at 1 at all. This query returns those as well. – wolfgangwalther Oct 31 '14 at 23:27
  • So, for example, add 7/8 8/9 and 9/10 to your database. You will see, that with your query, those rows will also be returned. If I use the query in the exact same way, that you have written it, you will also see, that 3/4 and 9/10 will not be in the output, that means the last element of the tree is missing. – wolfgangwalther Oct 31 '14 at 23:33