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 |
|
samirissa
Starting Member
4 Posts |
 Posted - 2011-07-04 : 10:58:27
|
| What is the best technique to retrieve records from three related tables in a sql server database where:*The first table row id is the parameter used to retrieve the data.*The second table has a foreign key relation with the first table row id*One row only of third table is related to the second table row in two ways: 1-the name of the third table is the value of one of the fields in the second table row2-the id of the the row of the third table is the same id as the row of the second table. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-07-04 : 11:17:46
|
Without an example of structure and data it is not easy to get you.But it sounds to me that there is no reason to have a third table.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
samirissa
Starting Member
4 Posts |
Posted - 2011-07-04 : 12:59:01
|
quote:
Originally posted by webfred
Without an example of structure and data it is not easy to get you.But it sounds to me that there is no reason to have a third table.
No, you're never too old to Yak'n'Roll if you're too young to die.
I have certain business rules that are resolved best by creating the above explained data structure, this will reduce significantly the database size.If an example would help:Let be table definitions: 1-table one is mainObject has constant field [id], 2-table two is sonObject has constant fields: [id], [mainObject_id], and [valueTableName] as fields ==sonObject has [id] as primary key and [mainObject_id] as foreign key.3-table three could be any of a set of tables with different table names, those tables share the same columns names: [id], and [value]. however the column [value] is different in its datatype from one table to the other.sonObject table is related to mainObject by the foreign key, each row of sonObject is related to a row in one of the tables representing table three, the [id] in sonObject and in table three is equal and the value of the [valueTableName] in sonObject is equal to the name of the table with which the row of sonObject is having a relation.Thanks for your suggestionssonObject has |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-07-04 : 13:48:47
|
should be something like this but the problem is that the value column datatype in the third table can be different so you can't select it as only one column using coalesce().So in my solution t1.value OR t2.value will be NULL.selectm.*,s.*,t1.value,t2.valuefrom mainObject mjoin sonObject s on m.id=s.mainObject_idleft join third_table_1 t1 on s.id=t1.id and s.valueTableName='third_table_1'left join third_table_2 t2 on s.id=t2.id and s.valueTableName='third_table_2'..
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
samirissa
Starting Member
4 Posts |
Posted - 2011-07-04 : 14:32:59
|
quote:
Originally posted by webfred
should be something like this but the problem is that the value column datatype in the third table can be different so you can't select it as only one column using coalesce().So in my solution t1.value OR t2.value will be NULL.selectm.*,s.*,t1.value,t2.valuefrom mainObject mjoin sonObject s on m.id=s.mainObject_idleft join third_table_1 t1 on s.id=t1.id and s.valueTableName='third_table_1'left join third_table_2 t2 on s.id=t2.id and s.valueTableName='third_table_2'..
No, you're never too old to Yak'n'Roll if you're too young to die.
|
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-07-04 : 14:33:35
|
Wouldn't it be better to have only ONE third table with a varchar column (or nvarchar) and then convert it when needed?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
samirissa
Starting Member
4 Posts |
Posted - 2011-07-04 : 14:33:53
|
THANKSquote:
Originally posted by webfred
should be something like this but the problem is that the value column datatype in the third table can be different so you can't select it as only one column using coalesce().So in my solution t1.value OR t2.value will be NULL.selectm.*,s.*,t1.value,t2.valuefrom mainObject mjoin sonObject s on m.id=s.mainObject_idleft join third_table_1 t1 on s.id=t1.id and s.valueTableName='third_table_1'left join third_table_2 t2 on s.id=t2.id and s.valueTableName='third_table_2'..
No, you're never too old to Yak'n'Roll if you're too young to die.
|
|
|
|
|
|
|
|
|
Any suggestions?