title: SQL query to fetch records based on related id in the same table
body: <p><strong>Table Structure:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/6WvvE.png" alt="enter image description here"></p>
  
  <p><strong>I Tried this:</strong></p>
  
  <pre><code>select Id, Name from Color where ParentId=4
  UNION
  select Id, Name from Color where ParentId=(select Id from Color where ParentId=4)
  </code></pre>
  
  <p>Above approach is static in this case I have to know all the Ids. I'm looking something dynamic approach since I would have only Color's ParentId value.</p>
  
  <p>As example: For ParentId=4 there are two records; Name Blue and Red their Id is 6 and 10 respectively. Now here, I need to get all the records where Id 6 and 10.</p>