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 |
|
ren225
Starting Member
2 Posts |
 Posted - 2004-08-02 : 01:14:46
|
| Hi,I've got three tables: Table1: Values(objectId, attributeId, value) Table2: Parents(objectId, attributeId, parentId) Table3: ParentValues(parentId, attributeId, value) Table1 contains objects and the values of the attributes within each object. There may be multiple attributes for a particular object.Table2 contains objects and the ids of the parents of these objects for a particular attribute.Table3 contains parent objects and the values of attributes within the parent. There may be multiple attributes for a particular parent.The value of an attribute for an object is given by the 'value' field in Table1. For instance, an entry in Table1 of (0001,1001,'valA') means that the object with id=0001 has a value of 'valA' for the attribute with id=1001.The 'parent value' of an attribute for an object is found by performing a join between Table2 and Table3. For instance, suppose we have an entry of (0001,1002,2003) in Table2. To find the parent value of the attribute 1002 for the object 0001, you would look up the 'value' field in Table3 where objectId=2003 (as this is the id of the parent of object 0001) and attributeId=1002.Basically, I'm trying to write a search query for my application which, given a particular attributeId and value, will find the ids of all the objects which have that value for that attribute as well as all the objects whose parents have that value for that attribute. This will also need to work when there are multiple conditions (e.g. attribute 1001 with value 'valA' AND attribute 1002 with value 'valB').I've considered using a UNION query similar to the following when trying to do the search with attribute=1001 and value='valA':( SELECT Table1.objectId, Table1.value FROM Table1 WHERE Table1.attributeId = 1001 AND Table1.value = 'valA')UNION( SELECT Table2.objectId, Table3.value FROM Table2, Table3 WHERE Table2.attributeId = 1001 AND Table2.attributeId = Table3.attributeId AND Table2.parentId = Table3.parentId AND Table3.value = 'valA')However, this will not work if there are multiple conditions for the search.Does anyone have any advice on how I could perform such a query? Any ideas would be much appreciated. Thanks. |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-08-02 : 01:33:08
|
| You might want to consider re-structuring your tables so that there is only one table/field that contains the attribute value and id. eg. Table1: Values(objectId, attributeId, value, parentId)In this case, the 'Parent' values will have a parentID of NULL. This allows you to have any number of heirarchical layers. You should keep in mind these sorts of things when you're designing tables. |
 |
|
|
ren225
Starting Member
2 Posts |
Posted - 2004-08-02 : 01:48:32
|
| Timmy, Thanks for the prompt reply. Just to clarify, are you suggesting that there should only be two tables:Table1: Values(objectId, attributeId, value, parentId)Table2: ParentValues(parentId, attributeId, value)Regards, Ren |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-08-02 : 02:16:39
|
| I was suggesting you merge two tables into one by using the strategy I outlined. I haven't seen any of your sample data so I can't really comment on the specifics. If you need more info, can you post some sample data for each of the tables and give an idea on what it's meant to do? Your original explanation is a bit vague |
|
|
|
|
|
|
|
|
Combining results from multiple tables