title: SQL - select both "child" and "parent" rows in a single query
body: <p>I have a database filled with a lot of "empty" records and I want to get rid of those orphaned records.</p>
  
  <p><strong>The database is structured like this;</strong></p>
  
  <ol>
  <li>A <em>child row</em> that contains the real entry (meta_value)</li>
  <li>A <em>parent row</em> that is "linked" to its child (by it's meta_key)</li>
  </ol>
  
  <hr>
  
  <p><code>
  |  meta_id        post_id       meta_key        meta_value   |
  |                                                            |
  |  011            301           ACF__P_01_01    Foo          |
  |  012            309           _ACF__P_01_01   field_5874d5 |
  |  013            321           ACF__P_01_02                 |
  |  014            316           _ACF__P_01_02   field_54290a |
  |  015            119           ACF__P_01_03    Bar          |
  |  016            101           _ACF__P_01_03   field_a04a88 |
  |  017            119           ACF__P_01_03                 |
  |  018            101           _ACF__P_01_03   field_a04a88 |
  |  019            149           ACF__P_01_03                 |
  |  020            111           _ACF__P_01_03   field_a04a88 |
  |  021            169           ACF__P_01_03    Foo Bar      |
  |  022            171           _ACF__P_01_03   field_a04a88 |
  </code></p>
  
  <hr>
  
  <p>It's easy to select all the (empty) "child" rows, with a query like this;</p>
  
  <pre><code>SELECT
      *
  FROM
      wp_postmeta
  WHERE
      wp_postmeta.meta_key LIKE '%ACF__%'
  AND
      wp_postmeta.meta_value LIKE ''
  </code></pre>
  
  <p>But this query only fetches the (empty) "child" rows, not their "parent" row.</p>
  
  <hr>
  
  <p>There are two logical conditions that bind the parent to the child;</p>
  
  <ol>
  <li>The <strong>first row</strong> is always the child, <strong>immediately followed</strong> by its parent.</li>
  <li>The parent row has the same "<strong>meta_key</strong>" value as it's child, but <strong>pre-fixed by an underscore</strong>.</li>
  </ol>
  
  <hr>
  
  <p>Is there any way to create a SQL query that selects the empty child records (like I did above) <strong>and</strong> it's parent as well?</p>
  
  <p>I prefer a single query to fetch them both - but when that's not possible, I could run two queries as well (<em>first one to fetch the parent, second one to get the empty children, I guess</em>).</p>
  
  <p>I use <strong>Navicat</strong> for my database management, so it must be plain SQL - no PHP.</p>
  
  <hr>
  
  <p><strong>The query should spit out the results like this;</strong></p>
  
  <hr>
  
  <p><code>
  |  meta_id        post_id       meta_key        meta_value   |
  |                                                            |
  |  013            321           ACF__P_01_02                 |
  |  014            316           _ACF__P_01_02   field_54290a |
  |  017            119           ACF__P_01_03                 |
  |  018            101           _ACF__P_01_03   field_a04a88 |
  |  019            149           ACF__P_01_03                 |
  |  020            111           _ACF__P_01_03   field_a04a88 |
  </code></p>
  
  <hr>
  
  <p>So both the empty-childs (no value in the "meta_value") and it's parent (both have the same "meta_key", where the parent has an underscore.</p>