title: Efficient approach to get two-dimensional datau using
body: <p>For the sake of example, let's say I have the following models:</p>
  
  <ul>
  <li>teams</li>
  <li>each team has an arbitrary amount of fans</li>
  </ul>
  
  <p>In SQL, this means you end up with the following tables:</p>
  
  <ul>
  <li>team: identifier, name</li>
  <li>fan: identifier, name</li>
  <li>team_fan: team_identifier, fan_identifier</li>
  </ul>
  
  <p>I am looking for an approach to retrieve:</p>
  
  <ul>
  <li>all teams, and</li>
  <li>for each team, the first 5 fans of which his/her name starts with an 'A'.</li>
  </ul>
  
  <p>What is an efficient approach to do this?</p>
  
  <ol>
  <li><p>In my current naive approach, I do <code>&lt;# teams&gt; + 1</code> queries, which is troublesome:</p>
  
  <ul>
  <li>First: <code>SELECT * FROM team</code></li>
  <li><p>Then, for each team with identifier <code>X</code>: </p>
  
  <pre><code>SELECT * 
  FROM fan 
    INNER JOIN team_fan 
      ON fan.identifier = team_fan.fan_identifier AND team_fan.team_identifier = X 
  WHERE fan.name LIKE 'A%' 
  ORDER BY fan.name LIMIT 5
  </code></pre></li>
  </ul></li>
  <li><p>There should be a better way to do this.</p></li>
  </ol>
  
  <p>I could first retrieve all teams, as I do now, and then do something like: </p>
  
  <pre><code>SELECT * 
  FROM fan 
  WHERE fan.name LIKE 'A%' 
  AND fan.identifier IN (
      SELECT fan_identifier 
      FROM team_fan 
      WHERE team_identifier IN (&lt;all team identifiers from first query&gt;)) 
  ORDER BY fan.name
  </code></pre>
  
  <p>However, this approach ignores the requirement that I need the first 5 fans for each team with his/her name starting with an 'A'. Just adding <code>LIMIT 5</code> to the query above is not correct.</p>
  
  <p>Also, with this approach, if I have a large amount of teams, I am sending the corresponding team identifiers back to the database in the second query (for the <code>IN (&lt;all team identifiers from first query&gt;)</code>), which might kill performance?</p>
  
  <p>I am developing against PostgreSQL, Java, Spring and plain JDBC.</p>