title: How to get child rows in DB2 for a specific subset of parent rows?
body: <p>I have a parent row and child rows on 7-10 separate tables, with a unique key tying the rows on the child tables to the parent row.  For a search feature,  I need to retrieve the parent and all the matching child rows, based on a query against the data in the parent table.  There are up to 5000 matching parent rows.  Currently we are re-running the query against each of the child tables, like:</p>
  
  <pre><code>select data from child 
  where key in (select key from parent where search_criteria)
  </code></pre>
  
  <p>This seems pretty inelegant especially as we continue to add more child tables.  Is there a better way to do this in DB2?</p>
  
  <p>Some of my thoughts:</p>
  
  <ul>
  <li>Will DB2 reuse the results of that select?  What about if there is a high volume of queries?</li>
  <li>I know the keys after the parent query, so I could pass them in the SQL, but that SQL would get crazy with 5000 keys, and presumably exceed the SQL limit.</li>
  <li>Would it be worth it to store the keys as a temp table, or would the setup / teardown be excessive?</li>
  </ul>