Referencing SQL Statement Within an SQL Statement

[eluser]RobbieL[/eluser]
I managed to get this done in Coldfusion, but having a bit of bother with CI. Basically, in my SQL WHERE statement, I need to reference a previous SQL statemenr to get the data I need. In this case, I want to list each school, with a list of the delegates underneath each school

These are my SQL statements at the moment:

Code:

#Get list of schools
$data['getSchools'] = $this->db->query("SELECT schoolName FROM schools GROUP BY schoolName");
        
#Get delegates
$data['getDelegates'] = $this->db->query("SELECT d.forename, d.surname, s.schoolName FROM delegates d LEFT JOIN schools s ON d.schoolId=s.id WHERE d.schoolId=s.id AND s.schoolName = '".$data['getSchools']."'");

And here's the PHP in my view:

Code:

<? foreach($getSchools->result() as $row)
{?>
    <?=$row->schoolName;?>
    
    <? foreach($getDelegates->result() as $row)
    {?>
        <?=$row->forename;?> <?=$row->surname;?>
    <?
    }?>
<?
}?>

The code doesn't spit out any areas, and does print the schools, just doesn't print the delegates from those schools. Not to sure were I'm going wrong.

I hope that all makes sense. Appreciate any help that can be given.
Cheers.