Hello,
I am trying to create a view, but to do that you need good SQL code. I am looking at two tables, I have represented them below. The current set of code works, but not the way I need it to. See the code and the Actual vs. Desired results.
Table Main
ID Sub_ID
----------------
1000 48
1001 36
1002 24
Table SubMain
ID Parent_ID
-------------------
48 36
36 24
24 12
12 null
Acutal Result
Main. Main. SubMain. SubMain. Submain.
ID Sub_ID Parent_ID1 Parent_ID2 Parent_ID3
-------------------------------------------------------------
1000 48 36 24 12
1001 36 24 12 null
1002 24 12 null null
Desired Result
Main. Main. SubMain. SubMain. Submain.
ID Sub_ID Parent_ID1 Parent_ID2 Parent_ID3
-------------------------------------------------------------
1000 48 12 24 36
1001 36 12 24 null
1002 24 12 null null
Is there a change I can make to the join or select statement that will enable me to supress null values? I have tried to change the select statement to:
The output changes, but not how I hoped:
Results
Main. Main. SubMain. SubMain. Submain.
ID Sub_ID Parent_ID1 Parent_ID2 Parent_ID3
-------------------------------------------------------------
1000 48 12 24 36
1001 36 null 12 24
1002 24 null null 12
The second SELECT statement actually gets me closer to what I want because the nulls are first, but I haven't been able to get the data to line up the way I need it to.
Any help is greatly appreciated.
Thanks - Charlie
I am trying to create a view, but to do that you need good SQL code. I am looking at two tables, I have represented them below. The current set of code works, but not the way I need it to. See the code and the Actual vs. Desired results.
Code:
Select Main.id, Main.Sub_ID, SM1.parent_id, SM2.parent_id, SM3.parent_id
From Main LEFT OUTER JOIN SubMain SM1 on (Main.Sub_ID = SM1.id)
LEFT OUTER JOIN SubMain SM2 on (SM1.parent_id = SM2.id)
LEFT OUTER JOIN SubMain SM3 on (SM2.parent_id = SM3.id)
Table Main
ID Sub_ID
----------------
1000 48
1001 36
1002 24
Table SubMain
ID Parent_ID
-------------------
48 36
36 24
24 12
12 null
Acutal Result
Main. Main. SubMain. SubMain. Submain.
ID Sub_ID Parent_ID1 Parent_ID2 Parent_ID3
-------------------------------------------------------------
1000 48 36 24 12
1001 36 24 12 null
1002 24 12 null null
Desired Result
Main. Main. SubMain. SubMain. Submain.
ID Sub_ID Parent_ID1 Parent_ID2 Parent_ID3
-------------------------------------------------------------
1000 48 12 24 36
1001 36 12 24 null
1002 24 12 null null
Is there a change I can make to the join or select statement that will enable me to supress null values? I have tried to change the select statement to:
Code:
Select Main.id, Main.Sub_ID, SM3.parent_id, SM2.parent_id, SM1.parent_id
The output changes, but not how I hoped:
Results
Main. Main. SubMain. SubMain. Submain.
ID Sub_ID Parent_ID1 Parent_ID2 Parent_ID3
-------------------------------------------------------------
1000 48 12 24 36
1001 36 null 12 24
1002 24 null null 12
The second SELECT statement actually gets me closer to what I want because the nulls are first, but I haven't been able to get the data to line up the way I need it to.
Any help is greatly appreciated.
Thanks - Charlie