CODEWITHSUNDEEP
mysqlrecursionself-join
James
James

Reputation: 93

mysql recursive self join

create table test(
container varchar(1),
contained varchar(1)
);

insert into test values('X','A');
insert into test values('X','B');
insert into test values('X','C');
insert into test values('Y','D');
insert into test values('Y','E');
insert into test values('Y','F');
insert into test values('A','P');
insert into test values('P','Q');
insert into test values('Q','R');
insert into test values('R','Y');
insert into test values('Y','X');

select * from test;

    mysql> select * from test;
    +-----------+-----------+
    | container | contained |
    +-----------+-----------+
    | X         | A         |
    | X         | B         |
    | X         | C         |
    | Y         | D         |
    | Y         | E         |
    | Y         | F         |
    | A         | P         |
    | P         | Q         |
    | Q         | R         |
    | R         | Y         |
    | Y         | X         |
    +-----------+-----------+
    11 rows in set (0.00 sec)

Can I find out all the distinct values contained under 'X' using a single self join?

EDIT

Like, Here X contains A, B and C A contains P P contains Q Q contains R R contains Y Y contains C, D and E...

So I want to display A,B,C,D,E,P,Q,R,Y when I query for X.

EDIT

Got it right by programming.

package com.catgen.helper;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.catgen.factories.Nm2NmFactory;

public class Nm2NmHelper {
    private List<String> fetched;
    private List<String> fresh;

    public List<String> findAllContainedNMByMarketId(Connection conn, String marketId) throws SQLException{
        fetched = new ArrayList<String>();
        fresh = new ArrayList<String>();
        fresh.add(marketId.toLowerCase());
        while(fresh.size()>0){
            fetched.add(fresh.get(0).toLowerCase());
            fresh.remove(0);
            List<String> tempList = Nm2NmFactory.getContainedNmByContainerNm(conn, fetched.get(fetched.size()-1));
            if(tempList!=null){
                for(int i=0;i<tempList.size();i++){
                    String current = tempList.get(i).toLowerCase();
                    if(!fetched.contains(current) && !fresh.contains(current)){
                        fresh.add(current);
                    }
                }
            }
        }
        return fetched;
    }
}

Not the same table and fields though. But I hope you get the concept. Thanks guys.

Upvotes: 1

Views: 4238

Answers (2)

user409460
user409460

Reputation:

What about reading the whole table into a php array, and determine the children via. a function which would call itself?

But this is not a good solution if the table has more than 10000 rows...

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838536

You can't get all the contained objects recursively using a single join with that data structure. You would need a recursive query but MySQL doesn't yet support that.

You could however construct a closure table, then you can do it with a simple query. See Bill Karwin's slideshow Models for heirarchical data for more details and other approaches (for example, nested sets). Slide 69 compares the different designs for ease of implementing 'Query subtree'. Your chosen design (adjacency list) is the most awkward of all four designs for this type of query.

Upvotes: 7

Related Questions