and i want to get a relational output as shown below. This is easily done by connect by prior in oracle but i am not able to do
in sql. please give me some idea how sud i move forward.
final output :
header_name header_type parent_name parent_type
ax_100 11 px_700 12
px_700 12 px_890 13
....and so on
I know you know how it works but, from your description, I have no clue as to what you're asking. It doesn't appear possible to identify which "a1" is identified in the "Data" table by the "Master" table.
Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. Why were you rude and failed to post DDL? Now we get to be your slaves who do your typing. But even worse, we need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.
There is no “master” concept in RDBMS. That was tape file systems and network data base. There is no generic, vague “id”, “name” or “parent” data elements in RDBMS. The very common Noob mistake you made is mimicking your old pointer chains in SQL! Not your fault; as you pointed out ORACLE (a really, really awful SQL!) has CONNECT BY PRIOR pointer chain traversals. Traversals do not exist in a declarative languages.
ANSI/ISO is fine for basic C.R.U.D. but since the manufacturers of various RDBMSs don't actually follow ANSI/ISO 100%, then trying to achieve pure ANSI/ISO does nothing more than to restrict you from using the more powerful extensions they provide. Certainly, it doesn't make your code portable.
I agree that the column name of "ID" is inappropriately non-descript but the concept of an "ID" column will continue until there's an RDBMS that actually allows for the simple construction and use of a solid natural key. A perfect example is that of a Customer table. Trying to come up with a meaningful bullet proof, easy to generate, use, and maintain natural key on such a table is virtually useless because it doesn't actually prevent the same customer from being entered into the table more than once.
The concept of a "master" table does exist in RDBMSs, although I do agree the name "Master" is as non-descript as "ID". The classic Invoice/Invoice Detail table combination is proof of the "master/slave" concept. The "slave" (InvoiceDetail row) must not exist before the "master" (Invoice row) does.
"Pointer Chains" are essential in SQL especially when it comes to hierarchies (you should actually know that based on the very book you've recommended). Even Nested Sets rely on them. They're also key to the summarization of data using certain aggregates based on things like PRECEEDING ROWS, etc. Without them, you would have to rely on the "natural order" in a table and we know how bad an idea that is since there are no such guarantees as to order.
Of course, if you only use SQL Server as a place to store data, none of that matters but don't expect any performance from your system if you do, especially in the "other" world of high performance batch processing.
The concept of a "master" table does exist in RDBMSs, although I do agree the name "Master" is as non-descript as "ID". The classic Invoice/Invoice Detail table combination is proof of the "master/slave" concept. The "slave" (InvoiceDetail row) must not exist before the "master" (Invoice row) does.
No, the concept of a Master goes back to tape files. The master was the current "data of record" which was updated by transaction tapes. The truth you are looking for our "strong entity" and "weak entity"in data modeling.
The weak entity exists only because it has a strong entity which it can reference. This is the invoice (strong) and the invoice details (weak).
Later, the "slave-master" terminology was used in Poynter-based network databases. The slaves were in a linked pointer chain that comes off of the master record. Did you ever worked with. IMS, IDMS or Total? My age is probably showing now:)
Heh... IMS in the early 80's and only enough to know that it needed to be replaced. Since they didn't want to replace it "just then", I worked around it by exporting the data to another type of system and made is so that people could get what they needed via those new fangled things called "PCs) without having to deliver several boxes worth of Green Bar to someone's desk just to get 30 numbers from it.
Thanks for the clarification as to what you mean by "Master". The bottom line on that is that we both agree that calling a table "Master" leaves a whole lot to be desired.