title: MySQL Closure Tables: How to pull data in the correct oreder when there are multiple parents for a child?
body: <p>I am using closure tables in a similar way to <a href="https://stackoverflow.com/a/8288201">this answer</a>. I have the following two tables:</p>
  
  <pre><code>CREATE TABLE `part` (
    `id` int(11) NOT NULL,
    `name` varchar(45) DEFAULT NULL,
    PRIMARY KEY (`id`),
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>Which is the table holding the data.</p>
  
  <pre><code>CREATE TABLE `BOM` (
    `ancestor_id` int(11) NOT NULL,
    `descendant_id` int(11) NOT NULL,
    `quantity` int(11) DEFAULT NULL,
    `length` int(11) DEFAULT NULL,
    PRIMARY KEY (`ancestor_id`,`descendant_id`),
    KEY `fk_BOM_part1_idx` (`ancestor_id`),
    KEY `fk_BOM_part2_idx` (`descendant_id`),
    CONSTRAINT `fk_BOM_part1` FOREIGN KEY (`ancestor_id`) REFERENCES `part` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_BOM_part2` FOREIGN KEY (`descendant_id`) REFERENCES `part` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>Which is the closure table. It uses a column length in order to record how deep you are in the tree. This is the test data I am using:</p>
  
  <pre><code>INSERT INTO `BOM` (`ancestor_id`, `descendant_id`, `quantity`, `length`) VALUES (1,1,3,0),(3,1,3,1),(3,3,1,0),(4,1,7,1),(4,4,1,0);
  INSERT INTO `part` (`id`, `name`, `part_type_id`) VALUES (1,'A',1),(2,'B',1),(3,'1',1),(4,'2',1);
  </code></pre>
  
  <p>In the case when each child has exactly one parent the following query outputs the entire tree in the correct order:</p>
  
  <pre><code>select 
      part.id AS id,
      concat(repeat('-', MAX(tree.length)), part.name) as name,
      group_concat(distinct tree.quantity) as quantity,
      group_concat(tree.ancestor_id order by tree.length desc separator ',') as breadcrumbs
  from part
      join BOM tree on
          part.id = tree.descendant_id
      group by part.id
      order by breadcrumbs;
  </code></pre>
  
  <p>However this does not work when there are multiple parents for the same child in the database. What I get is:</p>
  
  <pre><code>1
  2
  -A
  </code></pre>
  
  <p>What I want is:</p>
  
  <pre><code>1
  -A
  2
  -A
  </code></pre>
  
  <p>I understand why this happens, but I do not know how to fix it. What query should I use to pull the entire tree in the correct order?</p>