title: Insight.Database One to Many Mapping from a JOIN using Auto Interface
body: <p>Insight.Database supports mapping One to Many results <strong>if the SQL has multiple SELECT statements</strong>, one for Parent and the for the children. See: <a href="https://github.com/jonwagner/Insight.Database/wiki/Specifying-Result-Structures" rel="nofollow">https://github.com/jonwagner/Insight.Database/wiki/Specifying-Result-Structures</a></p>
  
  <pre><code>CREATE PROC GetBeerAndPossibleGlasses AS
  SELECT * FROM Beer
  SELECT b.BeerID, g.* FROM Beer JOIN Glasses ON (...)
  
  class Beer
  {
      public int ID;
      public IList&lt;Glass&gt; Glasses;
  }
  
  var results = connection.Query("GetBeerAndPossibleGlasses", Parameters.Empty,
      Query.Returns(Some&lt;Beer&gt;.Records)
          .ThenChildren(Some&lt;Glasses&gt;.Records);
  </code></pre>
  
  <p><strong>Can the one-to-many mapping be done using a single join query?</strong> I believe this is supported in Petapoco. (I am using Auto Interface implementation but I would like to know if there is any way at all to accomplish it.) e.g.</p>
  
  <pre><code>CREATE PROC GetBeerAndPossibleGlasses AS    
  SELECT b.*, g.* FROM Beer JOIN Glasses ON (...)
  </code></pre>
  
  <p>I have relatively complex query where I need to return parent and children as above. It works if I create multiple SELECT statments but my select statement look like below:</p>
  
  <pre><code>SELECT * FROM Beer WHERE &lt;Complex WHERE clause&gt;
  SELECT b.BeerID, g.* FROM Beer JOIN Glasses ON (...) &lt;Complex WHERE clause&gt;
  </code></pre>
  
  <p>I am worried that the query is as efficient as a single join. </p>