how do you do a script when the relationship between tables is defined in a table

Question

header

ID   Description Category

1    header         A

detail

ID  Description Product

3    itemA        A

4    itemB        B

tableDefinition

fromTable    fromTableID   toTable         toTableID

header        1                     detail          3

header        1                     detail          4

the relationship of tableA and tableB is defined in tableDefinition.

So how do I script the SQL to show the header and the details?

Answer 1

Is this what you are looking for?

select h.Description FromTable,h.ID FromTableID,'Detail' ToTable, d.ID ToTableID from header h
Cross join detail d

Regards,Eshwar.

---

Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

Answer 2

While I generally would not build a relationship between two tables in this manner usually (I would almost certainly design the relationship directly using Foreign Key constraints), you could do it this way:

select *
from   header
              join tableDefinition
                       on header.fromTable = 'header'
                            and tableDefinition.toTable = 'detail'
                            and header.id = tableDefinition.fromTableId
               join detail
                       on detail.id = tableDefinition.toTableId

You will need to make sure that the structure of tableDefinition is very well maintained (since you won't have any RDBMS support to make sure that id/tablename values are correct.

---

Louis

Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

Answer 3

Thanks for the replies. I failed to state that "Header" and "Detail" can be any name, depends on what is defined in the definition table, tableDefinition.

Answer 4

Generally you need a bunch of dynamic sql  generation for different tasks.

Please state all relevant assumptions explicitly.  For example,  your metadata tableDefinition table says nothing about columns. Which column value = 3 ? guess column name is "ID", but there should be no guess work in such case. Is any possible table of the same structure and what this structure is?  What are input parameters (table names or what) to construct the query?

---

Serg