I have inherited a database that I am not happy with the structure of. In the system there are components on the web application it reads as Level1, Level2, Level3. The tables are set up as
The component table houses the level name for all three level tables. I have built three different queries to pull the individual level name but I need to combine these three queries to get the full string as it appears in the application. Below are my three queries, any help you can provide I would appreciate. I have tried Union and Intersect statements and neither have worked.
Select b.PRDCT_CMPNT_ID as Level_1_ID, a.PRDCT_CMPNT_NM as Level_1_Name from PRDCT_CMPNT a join PRDCT_CMPNT_LVL_1 b on b.PRDCT_CMPNT_ID=a.PRDCT_CMPNT_ID
Select b.PRDCT_CMPNT_ID as Level_2_ID, b.LVL_1_PRDCT_CMPNT_ID, a.PRDCT_CMPNT_NM as Level_2_Name from PRDCT_CMPNT a join PRDCT_CMPNT_LVL_2 b on a.PRDCT_CMPNT_ID=b.PRDCT_CMPNT_ID join PRDCT_CMPNT_LVL_1 c on c.PRDCT_CMPNT_ID=b.LVL_1_PRDCT_CMPNT_ID
Select b.PRDCT_CMPNT_ID as Level_3_ID, a.PRDCT_CMPNT_NM as Level_3_Name, b.LVL_2_PRDCT_CMPNT_ID as Level_2_ID, c.LVL_1_PRDCT_CMPNT_ID as Level_1_ID from PRDCT_CMPNT a join PRDCT_CMPNT_LVL_3 b on b.PRDCT_CMPNT_ID=a.PRDCT_CMPNT_ID join PRDCT_CMPNT_LVL_2 c on b.LVL_2_PRDCT_CMPNT_ID=c.PRDCT_CMPNT_ID join PRDCT_CMPNT_LVL_1 d on c.LVL_1_PRDCT_CMPNT_ID=d.PRDCT_CMPNT_ID