Multiple aliases to the same table in a query?
446932Aug 31 2005 — edited Sep 1 2005I need to know how to make multiple table aliases to the same table using the query expression framework.
Basically, we have the following:
object ARTIFACT has a collection of ATTRIBUTE_VALUE objects
ATTRIBUTE_VALUE has a value and a reference to an ATTRIBUTE_DEF object
ATTRIBUTE_DEF has a name and a type.
So, I could have an ARTIFACT that has two ATTRIBTE_VALUES, one for the "string1" ATTRIBUTE_DEF, one for the "string2" ATTRIBUTE_DEF.
Let's say I'm querying for artifacts where the "string1" attribute's value is "foo" and the "string2" attribute's value is "bar":
When I perform a query, TopLink generates the following SQL:
SELECT A.*
FROM ARTIFACT A, ATTRIBUTE_VALUE V
WHERE
(
V.VALUE="foo" and V.ATTRIBUTE_DEF_ID=12 // id for "string1"
)
AND
(
V.VALUE="bar" and V.ATTRIBUTE_DEF_ID=13 // id for "string2"
)
AND
(
V.ARTIFACT_ID=A.ID
)
This is obviously not going to work. What I need it to do is the following:
SELECT A.*
FROM ARTIFACT A, ATTRIBUTE_VALUE V1, ATTRIBUTE_VALUE V2
WHERE
(
V1.VALUE="foo" and V1.ATTRIBUTE_DEF_ID=12 AND V1.ARTIFACT_ID=A.ID
)
AND
(
V2.VALUE="bar" and V2.ATTRIBUTE_DEF_ID=13 AND V2.ARTIFACT_ID=A.ID
)
I can't figure out how to set table aliases in the ExpressionBuilder framework. Is this possible?