Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Silenz
Starting Member
7 Posts |
 Posted - 2005-08-17 : 14:56:54
|
| I need to know if this is possible. The way this database is set up has me at my wits end. I need to link the value in the column Table_ID of table FORMS to the table associated with that number. FORMSTable_ID, Form_Name, ....123, AForm234, BFormTABLEA123 ID, Desc1, abc2, defTABLEB123ID, Name1, xyz2, zyxTABLEA234ID, DESC4, ...5, ...TABLEB234ID, Name1, 9994, xxxWhat I would like to see...Form_Name, Desc, NameAFORM, abc, xyzAFORM, def, zyxBFORM, NULL, 999BFORM, ..., xxxBFORM, ..., NULLAnother row could be added to the FORMS table at any time. This would cause the other 2 tables to be created (TABLEA... and TABLEB...)At this time the best solution I can see is to create a Union for each of the pairs of tables. However, this would take some time and would have to be updated for each new row that was created. Any help would be appreciated. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-17 : 17:36:07
|
| Wow, you have obscured what you are really doing so well that it just is not making much sense. My first thought is that your table design is probably the real culprit, but perhaps you could convince me otherwise with a real-world example instead of just gibberish. What are TableA123 and TABLEA234? Why are they separate tables? Why do you have a pointer in your data to tell you which table to join to? It is more common to have your tables normalized and the pointer in one table points to the ROWS in another table that it joins with, not to the table.However, if you think that a UNION of the pairs of tables would solve your problem, then why not create a VIEW that does just that. A VIEW is dynamic so there's no updating necessary as new rows are added.---------------------------EmeraldCityDomains.com |
 |
|
|
Silenz
Starting Member
7 Posts |
Posted - 2005-08-17 : 18:25:48
|
| Believe me. I wish I had created the database. Unfortunately it is widely used. Well maybe used to be. Witness 5.2. It has the schema of the Forms, Sections, Questions in 3 tables that I can nicely match up to give me FormName, Section, QuestionName. It has the answer and rollup data in 4 different tables.EvalHeader123, EvalSection123, EvalQuest123, EvalPoints123. Unfortunately there is no link between the Forms table and the 4 different Eval tables except the number at the end of the table name.Yeah, I was totally blown away looking at the structure of this. Each time a new form is created a row is inserted into the Forms table. The four Evaluation tables are created with the number on the end. Long story short. I want to get information out of the Forms, Sections, and Questions tables and match it up with the Evaluation Information. I have very little permissions as not only is it Proprietary 'Witness', but it is a linked server. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-18 : 14:25:55
|
Are you really saying that each time a new form is created, it causes 4 more tables to be created? Oh my... What about creating your own properly structured database for your querying, and then populating it periodically from the Witness system?---------------------------EmeraldCityDomains.com |
|
|
|
Silenz
Starting Member
7 Posts |
Posted - 2005-08-18 : 15:03:25
|
| Yeah, Kind of what I was thinking I would have to do. Was just hoping that there would be a dynamic way of doing it instead of every time they create a form having to go in and update everything to include the 4 new tables. Especially since they usually create 4 or 5 Forms at a time at 7 different sites. You can see how this can become quite a few tables to update. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-18 : 18:24:09
|
| You might be able to come up with something fancy by using the Information_Schema views in order to get a list of the tables.SELECT *FROM INFORMATION_SCHEMA.TablesWHERE TABLE_NAME LIKE 'Eval%'Then maybe a little fancy footwork with a WHILE statement to build some Dynamic SQL? Just tossing out ideas here...---------------------------EmeraldCityDomains.com |
|
|
|
|
|
|
|
|
Join to Multiple Tables via a field value