I'm trying to join together 2 tables with different schema's and I'm not exactly sure how or if it's possible. I could use some advice / guidance.
The first table is a listing of products and is structured like this:
Product_data
+-----------------------------+-------------+--------------+---------------------+--------------+
| Product ID | Product_SKU | Product_Name | Product_Update_Date | Product_Type |
+-----------------------------+-------------+--------------+---------------------+--------------+
| 1 | ER12345 | Ring 1 | 10/30/2015 | Ring |
+-----------------------------+-------------+--------------+---------------------+--------------+
| 2 | ER56323 | Ring 2 | 11/1/2015 | Ring |
+-----------------------------+-------------+--------------+---------------------+--------------+
| 3 | NK3467 | Necklace 1 | 11/4/2015 | Necklace |
+-----------------------------+-------------+--------------+---------------------+--------------+
| 4 | ER1034 | Ring 3 | 10/22/2015 | Ring |
+-----------------------------+-------------+--------------+---------------------+--------------+
The second table is structured like this:
Product_series
+-----------+-----------+------------+-------------------+
| Series_ID | Main_Item | Child_Item | Relationship_Type |
+-----------+-----------+------------+-------------------+
| 1 | ER12345 | ER56323 | 1 |
+-----------+-----------+------------+-------------------+
| 3 | ER12345 | ER1034 | 1 |
+-----------+-----------+------------+-------------------+
| 4 | ER56323 | ER12345 | 1 |
+-----------+-----------+------------+-------------------+
| 5 | ER56323 | ER1034 | 2 |
+-----------+-----------+------------+-------------------+
| 6 | ER1034 | ER12345 | 1 |
+-----------+-----------+------------+-------------------+
| 7 | ER1034 | ER56323 | 1 |
+-----------+-----------+------------+-------------------+
What I want to accomplish is to Join these 2 tables together so that I can see across 1 row, all of the product data, as well as what other products are "child" products for this products.
The best I can think is to use some kind of WHEN CASE statement but I'm not sure if this is the best or easiest approach. Ultimately what I'm looking to do is to be able to ready across each row and find out what the "Series" products are for each product in the main table. I'm trying to flatten the child table and join it to the main.
Example of what I'm trying to do.
Product_SKU Series_items
----------- ------------
ER12345 ER5623, ER1034, NK3467
LINK TO SQL FIDDLE: http://sqlfiddle.com/#!9/a0f93 with Schema already setup
Any help would be appreciated
