MySql join to return three rows per type, regardless of how many rows there are

I am trying to join these two tables I created so that the resultset has 3 entries for each type.

Data table

mysql> SELECT * FROM data;
+--------------+-----------+
| Labels       | Data      |
+--------------+-----------+
| Component    | 1325.1988 |
| Component    |  554.1652 |
| Component    |  105.4002 |
| Development  |  203.4163 |
| Development  |   59.4500 |
| Development  |   20.4498 |
| Flash Assets |  285.5334 |
| Flash Assets |  302.1501 |
| Flash Assets |   61.1836 |
| Release      |    0.6000 |
| Release      |    2.3666 |
| Repackage    |  416.2169 |
| Repackage    | 5195.0839 |
| Repackage    |    4.5667 |
| Source Diff  |    1.9000 |
| Source Diff  |    0.4000 |
+--------------+-----------+

Types table

mysql> SELECT * FROM types ORDER BY Labels;
+------------------------+------+
| Labels                 | Data |
+------------------------+------+
| Component              |    0 |
| Component              |    0 |
| Component              |    0 |
| Development            |    0 |
| Development            |    0 |
| Development            |    0 |
| Flash Assets           |    0 |
| Flash Assets           |    0 |
| Flash Assets           |    0 |
| Release                |    0 |
| Release                |    0 |
| Release                |    0 |
| Repackage              |    0 |
| Repackage              |    0 |
| Repackage              |    0 |
| Source Diff            |    0 |
| Source Diff            |    0 |
| Source Diff            |    0 |
+------------------------+------+

Current Query:

mysql> SELECT * FROM data d LEFT JOIN types t on t.Labels = d.Labels;
+--------------+-----------+--------------+------+
| Labels       | Data      | Labels       | Data |
+--------------+-----------+--------------+------+
| Component    | 1325.1988 | Component    |    0 |
| Component    | 1325.1988 | Component    |    0 |
| Component    | 1325.1988 | Component    |    0 |
| Component    |  554.1652 | Component    |    0 |
| Component    |  554.1652 | Component    |    0 |
| Component    |  554.1652 | Component    |    0 |
| Component    |  105.4002 | Component    |    0 |
| Component    |  105.4002 | Component    |    0 |
| Component    |  105.4002 | Component    |    0 |
| Development  |  203.4163 | Development  |    0 |
| Development  |  203.4163 | Development  |    0 |
| Development  |  203.4163 | Development  |    0 |
| Development  |   59.4500 | Development  |    0 |
| Development  |   59.4500 | Development  |    0 |
| Development  |   59.4500 | Development  |    0 |
| Development  |   20.4498 | Development  |    0 |
| Development  |   20.4498 | Development  |    0 |
| Development  |   20.4498 | Development  |    0 |
| Flash Assets |  285.5334 | Flash Assets |    0 |
| Flash Assets |  285.5334 | Flash Assets |    0 |
| Flash Assets |  285.5334 | Flash Assets |    0 |
| Flash Assets |  302.1501 | Flash Assets |    0 |
| Flash Assets |  302.1501 | Flash Assets |    0 |
| Flash Assets |  302.1501 | Flash Assets |    0 |
| Flash Assets |   61.1836 | Flash Assets |    0 |
| Flash Assets |   61.1836 | Flash Assets |    0 |
| Flash Assets |   61.1836 | Flash Assets |    0 |
| Release      |    0.6000 | Release      |    0 |
| Release      |    0.6000 | Release      |    0 |
| Release      |    0.6000 | Release      |    0 |
| Release      |    2.3666 | Release      |    0 |
| Release      |    2.3666 | Release      |    0 |
| Release      |    2.3666 | Release      |    0 |
| Repackage    |  416.2169 | Repackage    |    0 |
| Repackage    |  416.2169 | Repackage    |    0 |
| Repackage    |  416.2169 | Repackage    |    0 |
| Repackage    | 5195.0839 | Repackage    |    0 |
| Repackage    | 5195.0839 | Repackage    |    0 |
| Repackage    | 5195.0839 | Repackage    |    0 |
| Repackage    |    4.5667 | Repackage    |    0 |
| Repackage    |    4.5667 | Repackage    |    0 |
| Repackage    |    4.5667 | Repackage    |    0 |
| Source Diff  |    1.9000 | Source Diff  |    0 |
| Source Diff  |    1.9000 | Source Diff  |    0 |
| Source Diff  |    1.9000 | Source Diff  |    0 |
| Source Diff  |    0.4000 | Source Diff  |    0 |
| Source Diff  |    0.4000 | Source Diff  |    0 |
| Source Diff  |    0.4000 | Source Diff  |    0 |
+--------------+-----------+--------------+------+

My goal with the left join was to get all of the labels from the Types table so that ther开发者_高级运维e would be Three entries for each label in the resultset. For some reason it comes out funky.

Desired Output:

+--------------+-----------+
| Labels       | Data      |
+--------------+-----------+
| Component    | 1325.1988 |
| Component    |  554.1652 |
| Component    |  105.4002 |
| Development  |  203.4163 |
| Development  |   59.4500 |
| Development  |   20.4498 |
| Flash Assets |  285.5334 |
| Flash Assets |  302.1501 |
| Flash Assets |   61.1836 |
| Release      |    0.6000 |
| Release      |    0      |
| Release      |    2.3666 |
| Repackage    |  416.2169 |
| Repackage    | 5195.0839 |
| Repackage    |    4.5667 |
| Source Diff  |    1.9000 |
| Source Diff  |    1.9000 |
| Source Diff  |    0      |
+--------------+-----------+

Here there are three entries for each label and the data is replaced with the zeros from the types table

Any help would be great.

---

Have you tried following query:

SELECT t.Labels, COALESCE(d.Data, 0) 
FROM data d 
LEFT JOIN types t on t.Labels = d.Labels;

1. Why you need data column in Types table
2. Consider ID column in Types table and TypeId in Data table

---

your query joined each label to each matching data, ie each of the 3 Components in Types to 3 Components in Data resulting in 9 rows.

You can alternatively do following -

Select 
    t.Labels,
    (select column1 from Data d where d.Label = t.Label),
    (select column2 from Data d where d.Label = t.Label),
    (select column3 from Data d where d.Label = t.Label)
from Types t

---

Warning: If you ever find yourself JOINing on something that is not actually a unique ID in some table, you probably want to think about it again.

Here is a somewhat elegant solution to make sure you get three rows for each label:

(SELECT d.Labels, d.Data
 FROM Data d)
UNION ALL
(SELECT d.Labels, 0 AS Data
 FROM Data d
 GROUP BY d.Labels
 HAVING Count(*) <= 2)
UNION ALL
(SELECT d.Labels, 0 AS Data
 FROM Data d
 GROUP BY d.Labels
 HAVING Count(*) <= 1)

By the end, we've added two dummy rows if there was only one to start with, and we've added only one dummy row if there were two to start with. You should always end up with three at the end, like you want.