Please consider the following:
There are two tables: 1) DATA Table and PREFIX Table
The "DATA" Table is a one with Recursive Relationship (parent child relation exists). The PREFIX Table is linked to "DATA" Table through the Prefix ID
PREFIX Table Contents:
Prefix ID || Prefix
1 || Hon'ble
2 || Justice
3 || Dr.
DATA Table Contents:
Data ID | Data Parent ID | Prefix ID (FK) | Data
1 | Null | 1 | AAAA
2 | 1 | 2 | BBBB
3 | 2 | 3 | CCCC
Here is the tricky part...
I desire to have a view of the DATA Table in the following manner:
Data ID | Data Value
1 | Hon'ble AAAA
2 | Hon'ble AAAA (BBBB)
3 | Hon'ble AAAA (BBBB)(CCCC)
Note 1: The Prefix used... is ALWAYS of the TOP MOST PARENT
Note 2: The Parent Record (Where Parent ID is null) - is not enclosed in Round Brackets -- AAAA instead of (AAAA)
Note 3: The children - are always encapsulated in Round Bracket
Note 4 - Related to Note 1 - Children Prefixes are NEVER USED.
Thanks in Advance.