title: How to use SQL to avoid queries within loops
body: <p>I currently have two related tables, one for 'groups' and another for 'items'. Items are stored in groups by an associated group ID. Very basic table relations but unfortunately my SQL is not very good so I'm unsure what is the best way to loop through all groups and, within them, loop through all items to end up with a list of all items categorised by their groups.</p>
  
  <p>Currently I have this code:</p>
  
  <pre><code>$query = "SELECT ID AS GRPID, Description FROM Groups_Inv ORDER BY SortNum ASC";
  try { 
    $result = odbc_exec($connect,$query); 
    if($result){ 
      while ($groups = odbc_fetch_array($result)) {
        echo "&lt;li title='".$groups['DESCRIPTION']."' class='category'&gt;
              &lt;h3&gt;&lt;span&gt;&lt;a class='category'&gt;".
                $groups['DESCRIPTION'].
             "&lt;/a&gt;&lt;/span&gt;&lt;/h3&gt;&lt;ul class='sub-menu'&gt;\n";
        $subquery = "SELECT ID AS ITEM_ID, Description
                     FROM Items_Inv WHERE grpID = ".$groups['GRPID']."";
        $subresult = odbc_exec($connect,$subquery);
        while ($items = odbc_fetch_array($subresult)) {
          echo "&lt;li class='sub_item' title='".$items['DESCRIPTION']."'&gt;
                &lt;input type='radio' class='addRow'&gt;
                  &lt;label for='".$items['ITEM_ID']."'&gt;".
                    $items['DESCRIPTION'].
                 "&lt;/label&gt;&lt;/li&gt;\n";
        }
        echo "&lt;/ul&gt;&lt;/li&gt;\n";
      }
    } 
    else{ 
      throw new RuntimeException("Failed to connect."); 
    } 
  } 
  catch (RuntimeException $e) { 
    print("Exception caught: $e");
  }
  </code></pre>
  
  <p>I know that having a SQL query within a loop is a very bad idea which is why I would like to optimise this code but my knowledge of SQL is unfortunately pretty limited outside of basic CRUD stuff. </p>
  
  <p>I would also like to be able to not display groups that have no items, but I can't really do that with this code. What would be the best way to be able to get all groups and associated items within one query, as well as being able to specify only selecting groups with associated items?</p>