August 1, 2004 at 11:19 pm
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 or hints would be much appreciated.
Ren
August 3, 2004 at 8:19 am
Ufff... quite complicated, especially if you want to make it use variable number of conditions - I suppose there can be more than two.
Well, probably you could start thinking along these lines, since it is possible to add as many conditions as necessary (e.g. as a dynamic SQL). Similar syntax could be used to find the objects with corresponding parents. Those endless EXISTS statements are probably not the best as performance goes, but also not so terrible - and currently I don't see any other way... maybe someone else will come up with better idea. Here goes :
SELECT Tbl.objectId, Tbl.value
FROM Table1 Tbl
WHERE EXISTS (select * from Table1 where attributeId = 1001 AND value = 'valA' AND objectId = Tbl.objectId)
AND EXISTS (select * from Table1 where attributeId = 1002 AND value = 'valB' AND objectId = Tbl.objectId)
HTH, Vladan
August 4, 2004 at 8:10 am
Hi, I think that you need to be a bit more specific about what exactely your tables hold.
it appears that you have "simplified" it and now it is confusing.
Are your objectids referring to the same id?
Are your attributes referring to the same attributes?
I guess I am hoping for something like:
Table1: DimensionType(pk_DimensionType,Dimensiontype)
Table 2: Part(pk_Part,Part)
Table 3: PartDimension(pk_PartDimension,fk_Part,fk_DimensionType,Dimension)
tal mcmahon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply