title: Mysql - How to select all parents of a list of children?
body: <p>I have a table where each record contains his father and this hierarchy may have up to 6 levels. The query below search all parents of the department named "DEPARTMENT ABCD". This query works perfectly. </p>
  
  <pre><code>SELECT D2.id, D2.name, D2.id_parent
  FROM (
      SELECT
          @d AS _id,
          (SELECT @d := id_parent FROM department WHERE id = _id) AS id_parent,
          @l := @l + 1 AS level
      FROM
          (SELECT 
              @d := (select id from department where name = 'DEPARTMENT ABCD'), 
              @l := 0
          ) initial_level,
          department D
      WHERE @d &lt;&gt; 0
  ) D1
  JOIN department D2 ON D1._id = D2.id
  ORDER BY D1.level DESC;
  </code></pre>
  
  <p>However, if I want to get the parents of more than one child at the same time (using like in the query below), I get the error: "Error Code: 1242. Subquery returns more than 1 row".</p>
  
  <p>Query with the error "Subquery returns more than 1 row":</p>
  
  <pre><code>SELECT D2.id, D2.name, D2.id_parent
  FROM (
      SELECT
          @d AS _id,
          (SELECT @d := id_parent FROM department WHERE id = _id) AS id_parent,
          @l := @l + 1 AS level
      FROM
          (SELECT 
              @d := (select id from department where name like 'DEPARTMENT %A%'), 
              @l := 0
          ) initial_level,
          department D
      WHERE @d &lt;&gt; 0
  ) D1
  JOIN department D2 ON D1._id = D2.id
  ORDER BY D1.level DESC;
  </code></pre>
  
  <p>How could I do to get the parents of more than one child at the same time?</p>
  
  <p>I added a sample in SQL Fiddle: <a href="http://sqlfiddle.com/#!9/f182fb/3" rel="nofollow">http://sqlfiddle.com/#!9/f182fb/3</a></p>