select while result found ...

Marcus

unread,

May 27, 2008, 4:09:41 AM5/27/08

to

Hy!

I have a very tricky question for you and I hope you might help me.

I use Mysql 4.1.9-Max on a SUSE linux server.

My database table structure looks like this:

number_old (varchar 8) (primary key)
number_new (varchar 8)

Examples:

old new
00000001 00000002
01234567 01234568
01234568 00000001
47110815 47110816
...

Now I want to search for a number and want to find the newest for it.

For example: i search for 01234567
The search-path would be
01324567 -> 01234568 -> 00000001 -> 00000002
The result for the query has to be: 00000002

How can i do this within one single query?

I hope you can help me with this problem.

Thx in advance!

Marcus

Paul Lautman

unread,

May 27, 2008, 8:25:25 AM5/27/08

to

What you're looking for is recursive SQL. This is not possible in pure SQL
in MySQL.

It would be ideal to be able to do this with a recursive function. I found a
page on the internet that says:
mysql> Functions and procedures can call themselves.
It then gives an example that ends with:
ERROR 1424 (HY000): Recursive stored functions and triggers are not allowed.

A rather strange post. However it can be done with a recursive procedure, as
long as you set max_sp_recursion_depth big enough.
Here is a very simplistic example:
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`lnp` $$
CREATE PROCEDURE `test`.`lnp` (in sn varchar(8), out n varchar(8))
BEGIN
SELECT number_new FROM test.pn where number_old = sn into n;
IF ISNULL(n) THEN
SET n := sn;
ELSE
CALL lnp(n,n);
END IF;
END $$
DELIMITER ;

set max_sp_recursion_depth = 10
SET @b = '';
call lnp('01234567',@b);
SELECT @b;

cvh@LE

unread,

May 28, 2008, 2:20:03 AM5/28/08

to

I dont necessarily see recursion as the way to solve this.
However, I may simply be wrong about what Marcus is trying to
accomplish.

To me, this:

>The search-path would be
>01324567 -> 01234568 -> 00000001 -> 00000002

does not imply recursion but self join(s).
Yet, Its quite unclear to me on what basis the '01234568 -> 00000001'
part is done. So my question is:
Can you describe how or why this query-part is done? Is it because it
is the previous row in the table or are the numbers '01234568' and
'00000001' in one way or another related to each other?

Besides, Marcus runs v4.1 and to my knowledge stored procedures only
work in v5+:

Stored procedures and functions are implemented beginning with MySQL
5.0. see: http://dev.mysql.com/doc/refman/4.1/en/ansi-diff-triggers.html

cvh@LE

unread,

May 28, 2008, 2:43:34 AM5/28/08

to

Forgive my stupidity. Of course it is quite clear how they are
interrelated. (I didnt have a coffee yet this morning)

Anyways.

A simple way to implement recursion in mysql is by using self joins
and then testing for the existence of values. This way can be used in
version previous to 5 without the use of procedures or functions:

mysql> create table testrek (oldi int(5),newi int(5));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into testrek values (1,2),(10,11),(11,1),(17,18);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from testrek;
+------+------+
| oldi | newi |
+------+------+
| 1 | 2 |
| 10 | 11 |
| 11 | 1 |
| 17 | 18 |
+------+------+
4 rows in set (0.00 sec)

mysql> select
-> t1.oldi,
-> CASE
-> WHEN NOT ISNULL(t5.newi) THEN t5.newi
-> WHEN NOT ISNULL(t4.newi) THEN t4.newi
-> WHEN NOT ISNULL(t3.newi) THEN t3.newi
-> WHEN NOT ISNULL(t2.newi) THEN t2.newi
-> WHEN NOT ISNULL(t1.newi) THEN t1.newi
-> END
-> newVal
->
-> from
-> testrek t1
-> left join
-> testrek t2 on t1.newi=t2.oldi
-> left join
-> testrek t3 on t2.newi=t3.oldi
-> left join
-> testrek t4 on t3.newi=t4.oldi
-> left join
-> testrek t5 on t4.newi=t5.oldi
->
-> where
-> t1.oldi IN (1,10,11,17);
+------+--------+
| oldi | newVal |
+------+--------+
| 1 | 2 |
| 10 | 2 |
| 11 | 2 |
| 17 | 18 |
+------+--------+
4 rows in set (0.00 sec)

mysql>

This can also be done by use of nested IF-Statements:
mysql> select
-> t1.oldi, IF(
-> NOT ISNULL(t5.newi),t5.newi,
-> IF(NOT ISNULL(t4.newi),t4.newi,
-> IF(NOT ISNULL(t3.newi),t3.newi,
-> IF(NOT ISNULL(t2.newi),t2.newi,
-> t1.newi
-> )
-> )
-> )
-> )
->
-> newVal
->
-> from
-> testrek t1
-> left join
-> testrek t2 on t1.newi=t2.oldi
-> left join
-> testrek t3 on t2.newi=t3.oldi
-> left join
-> testrek t4 on t3.newi=t4.oldi
-> left join
-> testrek t5 on t4.newi=t5.oldi
->
-> where
-> t1.oldi IN (1,10,11,17);
+------+--------+
| oldi | newVal |
+------+--------+
| 1 | 2 |
| 10 | 2 |
| 11 | 2 |
| 17 | 18 |
+------+--------+
4 rows in set (0.00 sec)

mysql>

To illustrate what is done here see the unfiltered result of this
query:

This implementation however has one downside: you need to know the max
number of self-joins/recursive joins before implementing the query.
However, in most cases you will know this and thus can implement the
query (using max# of self joins) accordingly

Hope this helps

Chris
cvh@LE

Paul Lautman

unread,

May 28, 2008, 6:32:20 AM5/28/08

to

My first comment is that you have made the SELECT part overly complicated by
using CASE or IFNULL instead of the COALESCE function which is made for
cases such as these:
COALESCE(t5.newi,t4.newi,t3.newi,t2.newi,t1.newi)

Next, whilst the recursion limit is 255, the OP will hit the JOIN limit (31
or 61 depending installation) far quicker.

It actually occured to me that an interative loop coded within a stored
function will do this with no limit.

cvh@LE

unread,

May 28, 2008, 8:57:10 AM5/28/08

to

Granted, I shall have a look at that

> Next, whilst the recursion limit is 255, the OP will hit the JOIN limit (31
> or 61 depending installation) far quicker.
>
> It actually occured to me that an interative loop coded within a stored
> function will do this with no limit.

The 31/61 limit for join is in fact a downside, however, since stored
procedures are not available in 4.1 this limit is something he will
have live with unless there is another way to solve this in 4.1

Marcus

unread,

May 29, 2008, 2:46:35 AM5/29/08

to

Thx Paul and cvh@le!!!

This helped me ! Great solution.

But one thing left....

I don't know the max number of self-joins/recursive joins. How can I
do this without it? I don't want to put 31 JOINS together, cause it
might be, that one number needs more joins. This might occur...

Any solutions for that kind of problem?

Greetings
Marcus

Captain Paralytic

unread,

May 29, 2008, 4:49:53 AM5/29/08

to

If you can do user functions then you can do an iterative process (a
loop) which will not suffer from any recursion limit. Depends on what
version of MySQL CREATE FUNCTION was introduced.