MySQL table JOIN - not working correctly

Question

I want to join two tables. Display table A with the corresponding values from table B.

TableA

        ID   |   NAMES
        1    |   jan
        2    |   san
        3    |   tani
        4    |   mike

TableB

       ID   |   VALUE
       1    |   groen
       1    |   geel
       1    |   oranja
       2    |   groen

QUERY

       SELECT * FROM TableA a LEFT JOIN TableB b ON a.id = b.id

OUTPUT

      1 - jan - groen
      1 - jan - geel
      1 - jan - oranja
      2 - san - groen
      3 - tani
      4 - mike

REQUIRED OUTPUT

      1 - jan - groen, geel, oranja
      2 - san - groen
      3 - tani
      4 - mike

I have changed the query to every possible thing (left join, left inner join, left outer join, right join, right inner join, right outer join, full join, join) in every possible order (select TableA.id, TableA.names TableB.id...etc.) If I understand the joining correctly, the type of join I am looking for is INNER LEFT JOIN - to display TableA with the corresponding values of TableB.

I am working with MySQL 5.6

Answer 1

Consider the following:

<?php

/*
DROP TABLE IF EXISTS tablea;

CREATE TABLE tablea
(ID SERIAL PRIMARY KEY
,NAME VARCHAR(12) NOT NULL UNIQUE
);

INSERT INTO tablea VALUES
(1,'jan'),
(2,'san'),
(3,'tani'),
(4,'mike');


DROP TABLE IF EXISTS tableb;

CREATE TABLE tableb
(ID   INT NOT NULL
,VALUE VARCHAR(12) NOT NULL
,PRIMARY KEY(id,value)
);

INSERT INTO tableb VALUES
(1,'groen'),
(1,'geel'),
(1,'oranja'),
(2,'groen');

SELECT a.id
     , a.name
     , b.value
  FROM tablea a
  LEFT
  JOIN tableb b
    ON b.id = a.id
 ORDER
    BY id
     , value;
+----+------+--------+
| ID | NAME | value  |
+----+------+--------+
|  1 | jan  | geel   |
|  1 | jan  | groen  |
|  1 | jan  | oranja |
|  2 | san  | groen  |
|  3 | tani | NULL   |
|  4 | mike | NULL   |
+----+------+--------+
*/

require('path/to/connection/stateme.nts');

$query = "
SELECT a.id
     , a.name
     , b.value
  FROM tablea a
  LEFT
  JOIN tableb b
    ON b.id = a.id
 ORDER
    BY id
     , value;
";

$result = mysqli_query($db,$query) or die(mysqli_error());

$old_array = array();

while($row = mysqli_fetch_assoc($result)){

$old_array[] = $row;

}


$new_array = array();

foreach ($old_array as $row) {
   $new_array[$row['id']]['name'] = $row['name'];
   $new_array[$row['id']]['value'][] = $row['value'];
}

$new_array = array_values($new_array); // reindex


print_r($new_array);

?>

Outputs:

Array
(
    [0] => Array
        (
            [name] => jan
            [value] => Array
                (
                    [0] => geel
                    [1] => groen
                    [2] => oranja
                )

        )

    [1] => Array
        (
            [name] => san
            [value] => Array
                (
                    [0] => groen
                )

        )

    [2] => Array
        (
            [name] => tani
            [value] => Array
                (
                    [0] =>
                )

        )

    [3] => Array
        (
            [name] => mike
            [value] => Array
                (
                    [0] =>
                )

        )

)

Answer 2

try this query

SELEct a.id,a.name, 
 GROUP_CONCAT(b.name)name 
 FROM a left JOIN b ON (a.id = b.id)
 GROUP BY a.id