0

Good Morning,

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
Component ->Level1
Component ->Level2
Component ->Level3

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.

Level 1

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

Level 2

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

Level 3

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
2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by tesuji
0

Your problem description is really hardly understood. Can you state examples with meaningful data for all three levels and also one on the result you are looking for?

I am vaguely supposing, your problem deals with hierarchies and trees in sql databases. If so, you might search for Joe Celko trees, who wrotes a complete book about SQL and trees.

-- tesu

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.