LEFT JOIN to get all from left table and not duplicate results from right in MySQLi

One SQL table contains realisations and the other got types for realisations. Correlation left join would be realisations.id = realisations_kind.realisation_id, my actual code, has no left join, I have been doing it just a dirty way.

        $s = $mysqli->prepare( "SELECT id, name FROM realisations" );
        $s->execute();
        $s->bind_result( $id, $name );
        $s->store_result();
        while( $s->fetch())
        {
            /***/
            $si = $mysqli->prepare( "SELECT kind FROM realisations_kind WHERE realisation_id = ?" );
            $si->bind_param( 'i', $id );
            $si->execute();
            $si->bind_result( $kind );
            $si->store_result();
            while( $si->fetch())
            {
                if(isset($kind)) {
                    $arrayKind[] = $kind;
                }

            }
           
            $return[] = array('id' => $id, 'name' => $name, 'kind' => $arrayKind);
            $arrayKind = null;
            /***/
        }

This will output such array

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Building 1
            [kind] => Array
                (
                    [0] => 0
                    [1] => 1
                )

        )

    [1] => Array
        (
            [id] => 2
            [name] => Building 2
            [kind] => Array
                (
                    [0] => 1
                )

        )

    ..................more

At index 0 I got key "kind" which got 2 results and at index 1 got "kind" 1 result; this is the proper result because this table has 3 records, two assigned to id1 and one to id2.

I don't know to do this via left join and group SQL clauses.

Left join results in all left table rows but duplicates the right table. If I set up group by it removes duplicates from the right but does not print out all results from the left table, giving only the first one.

Solution

Of course LEFT JOIN gives duplicates in left table, that's the nature of joins. SQL does not return nested tables. All is combined into one single table. You have to structure the data in PHP.

SELECT `r`.`id`, `r`.`name`, `k`.`kind`
FROM
  `realisations` `r`
LEFT JOIN
  `realisations_kind` `k`
ON `k`.`realisation_id` = `r`.`id`
;

PHP could have something like that

while( $s->fetch())
{
  if(!isset($data[$id]))
  { 
    $data[$id] =
    [ 'id'   => $id,
      'name' => $name,
      'kind' => [$kind]
    ]
  }
  else
  {
    $data[$id]['kind'][] = $kind;
  }
}

If you want a zero based numeric array, just do a $data = array_values($data) after the loop.