mysql recursive self join

Question

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.

TIA

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.

Answer 1

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 48 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.

Answer 2

What about reading the hole 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...