Multiple results join

[eluser]Unknown[/eluser]
Hi All,

This is my first time here and I'm stuck when trying to write a sql query.

I have 3 tables named: patient, group and group_patient.

group_patient contains the id's of patient and group.

A patient can have multiple groups. I have the following query:

$this -> db -> select('patient.naam as patient_naam, ecaris_nr, geslacht, group.naam as group_naam');
$this -> db -> from('patient');
$this -> db -> join('group_patient', 'group_patient.patient = patient.id');
$this -> db -> join('group', 'group_patient.group = group.id');
$result = $this->db->get();

Yet i'm getting a array with the same patient and echt a different group, what I want is that the patient object has an array of groups.

What am I doing wrong?

[eluser]Stefan Hueg[/eluser]
[quote author="khallouki" date="1375184062"]
What am I doing wrong?
[/quote]

Nothing, thats how JOINs work. What you want to do can be achieved by this code:

Code:

$this->db->select('patient.id, patient.naam as patient_naam, ecaris_nr, geslacht, group.naam as group_naam, group.id as group_id');
$this->db->from('patient');
$this->db->join('group_patient’, ‘group_patient.patient = patient.id');  
$this->db->join('group’, ‘group_patient.group = group.id');
$result = $this->db->get();
$patients = array();
while ($row = mysql_fetch_assoc($result->result_id))
{
if (!isset($patients[$id]))
{
  $patients[$id] = array(
   'patient_naam' => $row['patient_naam'],
   'ecaris_nr' => $row['ecaris_nr'],
   'geslacht' => $row['geslacht'],
   'groups' => array()
  );
}
$patients[$id]['groups'][$row['group_id']] = $row['group_naam'];
}

[eluser]Unknown[/eluser]
[quote author="Stefan Hueg" date="1375189727"][quote author="khallouki" date="1375184062"]
What am I doing wrong?
[/quote]

Nothing, thats how JOINs work. What you want to do can be achieved by this code:

Code:

$this->db->select('patient.id, patient.naam as patient_naam, ecaris_nr, geslacht, group.naam as group_naam, group.id as group_id');
$this->db->from('patient');
$this->db->join('group_patient’, ‘group_patient.patient = patient.id');  
$this->db->join('group’, ‘group_patient.group = group.id');
$result = $this->db->get();
$patients = array();
while ($row = mysql_fetch_assoc($result->result_id))
{
if (!isset($patients[$id]))
{
  $patients[$id] = array(
   'patient_naam' => $row['patient_naam'],
   'ecaris_nr' => $row['ecaris_nr'],
   'geslacht' => $row['geslacht'],
   'groups' => array()
  );
}
$patients[$id]['groups'][$row['group_id']] = $row['group_naam'];
}

[/quote]

Thank you very much!