I am wanting to use data from multiple tables, Mixing the data to form a result, whereas the first (Data) table all data is resulted, AND (IF) there is a valid entry in the second (Value) table that this is appended into the resulting data
Example table/data as follows.
CREATE TABLE `test`.`Data` (`id` INT NOT NULL, `product` CHAR(1) NULL, `Fruit` VARCHAR(45) NULL, PRIMARY KEY (`id`));
INSERT INTO `test`.`data` VALUES (101, 'A', 'Apple'), (102, 'B', 'Banana'), (103, 'D', 'Grape'), (104, 'E', 'Orange'), (105, 'C', 'Pear');
CREATE TABLE `test`.`Value` (`Product` CHAR(1) NOT NULL, `value` INT NULL);
INSERT INTO `test`.`Value` VALUES ('A', 1), ('A', 2), ('A', 3), ('B', 1), ('E', 1), ('F', 1), ('G', 1), ('G', 2);
Data Table
+------+----------+----------+
| id | product | Fruit |
+------+----------+----------+
| 101 | A | Apple |
| 102 | B | Banana |
| 103 | D | Grape |
| 104 | E | Orange |
| 105 | C | Pear |
+------+----------+----------+
value Table
+----------+--------+
| product | Value |
+----------+--------+
| A | 1 |
| A | 2 |
| A | 3 |
| B | 1 |
| E | 1 |
| F | 1 |
| G | 1 |
| G | 2 |
+----------+--------+
Wanted Result
+------+----------+----------+
| id | product | Fruit |
+------+----------+----------+
|101 | A | Apple |
|101-1 | A-1 | Apple |
|101-2 | A-2 | Apple |
|101-3 | A-3 | Apple |
|102 | B | Banana |
|102-1 | B-1 | Banana |
|103 | D | Grape |
|104 | E | Orange |
|104-1 | E-1 | Orange |
|105 | C | Pear |
+------+----------+----------+
I am using a test database for this at the moment, just having trouble forming a working SELECT statement to get the desired result