无法弄清LEFT JOIN从左表获取所有内容并且不在MYSQLI中右侧重复结果

Question

This is example of my code wich get's information from two mysql tables, rather easy stuff.

One 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

so as you can see i got at index 0 key "kind" which got 2 results and at index 1 got "kind" 1 result, this is proper result because this table got 3 records two assigned to id1 and one to id2.

I have been trying to do this via left join and group sql statement but i can't find out how to do that, i did that several times in past but i forgot. Now i fill bit stupid to ask such basic thing there.

Left join results left table all rows result but duplicates right table or if i setup group by it remove duplicates from right but does not print out all results from left table, gives only first one :(

Answer 1

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.