Jane WIlkie Jane WIlkie - 1 year ago 36
MySQL Question

Need to generate records, one to many. MySQL

I have a table called testPPDS that contains my individual records. ProjectUID is unique ( although there is no enforcement of a unique key ).Environment is blank.

+------------+-------------+-------------+
| ProjectUID | ProjectName | environment |
+------------+-------------+-------------+
| 1 | Project1 | |
| 2 | Project2 | |
+------------+-------------+-------------+


I have another table called testMASTER that contains the environment info I need to populate the testPPDS table. It's data is below...

+------------+-------------+
| ProjectUID | environment |
+------------+-------------+
| 1 | DEV1 |
| 1 | DEV2 |
| 2 | DEV3 |
| 2 | DEV4 |
+------------+-------------+


I am trying to generate a record detailing ProjectUIDs and all the environments they show up in. I would then use this SELECT to populate a new table.

+------------+-------------+-------------+
| ProjectUID | ProjectName | environment |
+------------+-------------+-------------+
| 1 | Project1 | DEV1 |
| 1 | Project1 | DEV2 |
| 2 | Project2 | DEV3 |
| 2 | Project2 | DEV4 |
+------------+-------------+-------------+


I am trying all kinds of joins to get this out there and none seem to be working. Has anyone ran across something like this before? I appreciate any advice anyone has.
JC

Answer Source

SELECT testMASTER.ProjectUID , testPPDS.Projectname , testMASTER.environment FROM testMASTER RIGHT JOIN testPPDS ON testPPDS.ProjectUID = testMASTER.ProjectUID

enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download