You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by T E Schmitz <ma...@numerixtechnology.de> on 2004/05/02 18:43:21 UTC
left outer join
Hello,
I have 3 tables SECTION and BRAND and MODEL. SECTION is related to BRAND
and to MODEL via two foreign keys. I would like to select ALL SECTIONs
whether the FKs are null or not and fetch the BRAND and MODEL attributes
in one SQL statement. In other words I need a double outer join:
SELECT
SECTION.SECTION_PK,
SECTION.SECTION_NAME,
SECTION.BRAND_FK,
SECTION.MODEL_FK,
BRAND.BRAND_NAME
MODEL.MODEL_NAME
FROM SECTION
left outer join MODEL on MODEL_PK =MODEL_FK
left outer join BRAND on BRAND_PK =BRAND_FK
I have seen some old discussion threads from 2002 suggesting that Torque
does not support outer joins. Is this still the case?
If so, can anyone suggest an efficient work-around? I'd rather not fetch
the other table's attributes in a loop.
=======================================================================
2nd Problem
-----------
Scenario as above but this time SECTION has two foreign key
relationships with the same table:
SELECT
SECTION.SECTION_PK,
SECTION.SECTION_NAME,
SECTION.BRAND_1_SKIPPED_FK,
SECTION.BRAND_2_SKIPPED_FK,
BRAND.BRAND_NAME (1)
BRAND.BRAND_NAME (2)
FROM SECTION
left outer join BRAND on BRAND_PK =BRAND_1_SKIPPED_FK
left outer join BRAND on BRAND_PK =BRAND_2_SKIPPED_FK
(the above obviously doens't work as SQL statement but ...)
How would I specify the same output columns twice?
How can I implement this logic using Torque?
--
Regards,
Tarlika Elisabeth Schmitz
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: ticked off
Posted by Saravana Kannan <sk...@engr.uky.edu>.
Hi,
Tarlika's reply seems to work. I haven't compiled it yet because I should
change several other places where I use a primitive int in the setter and getter
methods. I believe her reply should work.
~Sarav
--
There are 10 kinds of people in the world,
those who know binary and those who don't.
Quoting T E Schmitz <ma...@numerixtechnology.de>:
> Have you set your schema to use defaultJavaType="object" ?
>
> Do you use Integer objects or int? If you use int your FK will assume an
> integer value the default value being 0 (zero). Only if you use objects
> as opposed to primitive values will you be able to set any column to null.
>
> As the DB can distinguish between null values and actual values you
> should always use defaultJavaType="object" in order to be able
> distinguish this in the application too.
>
> Tarlika Elisabeth Schmitz
>
> Saravana Kannan wrote:
> > AnswerGroup ansgrp = new AnswerGroup();
> > AnswerGroup ans = new Answer();
> > .
> > (calling setter methods)
> > .
> > ansgrp.save();
> > ansgrp.addAnswer(ans);
> > ans.save();
> >
> > And I get the Torque Exception which is the result of an Oracle constraint
>
> > error. The Oracle error was something of the sort-
> > "Foreign key 0 does not exist in ExprNode"
> >
> > The question here is - Why is Torque/Village setting ANSWER.ExprNodeFK to
> "0"
> > when I never called ExprNode.addAnswer(ans) or ans.setExprNodeFK() ? I want
> the
> > ANSWER.ExprNodeFK to be null. I'm unable to create foreign keys which are
> null.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: ticked off
Posted by T E Schmitz <ma...@numerixtechnology.de>.
Have you set your schema to use defaultJavaType="object" ?
Do you use Integer objects or int? If you use int your FK will assume an
integer value the default value being 0 (zero). Only if you use objects
as opposed to primitive values will you be able to set any column to null.
As the DB can distinguish between null values and actual values you
should always use defaultJavaType="object" in order to be able
distinguish this in the application too.
Tarlika Elisabeth Schmitz
Saravana Kannan wrote:
> AnswerGroup ansgrp = new AnswerGroup();
> AnswerGroup ans = new Answer();
> .
> (calling setter methods)
> .
> ansgrp.save();
> ansgrp.addAnswer(ans);
> ans.save();
>
> And I get the Torque Exception which is the result of an Oracle constraint
> error. The Oracle error was something of the sort-
> "Foreign key 0 does not exist in ExprNode"
>
> The question here is - Why is Torque/Village setting ANSWER.ExprNodeFK to "0"
> when I never called ExprNode.addAnswer(ans) or ans.setExprNodeFK() ? I want the
> ANSWER.ExprNodeFK to be null. I'm unable to create foreign keys which are null.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Creating records with value NULL for FKs
Posted by Saravana Kannan <sk...@engr.uky.edu>.
Hi,
Related in the sense, the assumption in your question is the reason for my
question. Looks like you got ticked off by my mail for some reason - if so,
sorry about that.
Can someone help me?
I will post me question again just in case -
I have a Torque Object called Answer which is related to AnswerGroup and
ExprNode. Answer can be either the child of AnswerGroup OR a child of ExprNode
(ie Answer has two FKs - one referencing AnswerGroup and the other referencing
ExprNode).
So I do something like-
AnswerGroup ansgrp = new AnswerGroup();
AnswerGroup ans = new Answer();
..
(calling setter methods)
..
ansgrp.save();
ansgrp.addAnswer(ans);
ans.save();
And I get the Torque Exception which is the result of an Oracle constraint
error. The Oracle error was something of the sort-
"Foreign key 0 does not exist in ExprNode"
The question here is - Why is Torque/Village setting ANSWER.ExprNodeFK to "0"
when I never called ExprNode.addAnswer(ans) or ans.setExprNodeFK() ? I want the
ANSWER.ExprNodeFK to be null. I'm unable to create foreign keys which are null.
How does one create a FK which is null? As is the norm, my foreign keys are of
type INTEGER in the project-schema.xml.
Ofcourse I could create a dummy ExprNode by hand that has PK = 0 and not use it
anywhere. But I want to find the better or proper way to take care of this
problem. Using ExprNode with PK=0 will be my last resort.
If the problem I mention is the way Torque works, then I would like to suggest a
solution to this problem as a feature of newer releases of Torque. Thank you.
~Sarav
--
There are 10 kinds of people in the world,
those who know binary and those who don't.
Quoting T E Schmitz <ma...@numerixtechnology.de>:
> I'm sorry but I can't see how the problem you describe is related to my
> question. My Question was whether outer joins are supported or not and
> if not, what the work-around could be.
>
>
> Regards,
>
> Tarlika Elisabeth Schmitz
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: left outer join
Posted by David Hainlin <dh...@comcast.net>.
Hello Tarlika,
I simply defined the view in the normal DDL way (create view...) and
after I was happy with the SQL view, I added it to the torque schema
xml. Torque thinks it is a table and it works great as long as you
don't try to update it... Here is a sample below:
CREATE OR REPLACE VIEW JOBS_VIEW ( POSITION,
ORDERING, SHORT_NAME ) AS SELECT
FFV.FLEX_VALUE AS POSITION,
FFV.ATTRIBUTE2 AS ORDERING,
JN.SHORT_NAME AS SHORT_NAME
FROM
FND_FLEX_VALUES FFV,
FND_FLEX_VALUE_SETS FFVS,
JOBS_NAME JN
WHERE
FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID AND
FFVS.FLEX_VALUE_SET_NAME LIKE 'SSI_HR_JOB%' AND
FFV.ENABLED_FLAG = 'Y' AND
FFV.ATTRIBUTE1='V' AND
FFV.FLEX_VALUE=JN.LONG_NAME(+)
ORDER BY ORDERING,POSITION;
then in DB-schema.xml
<table name="JOBS_VIEW" skipSql="true"
description="<b style='color:blue;'>CUSTOM</b> Table of
Job names, their short name and ordering. Custom view of
FND_FLEX_VALUES, FND_FLEX_VALUE_SETS for SSI_HR_JOB%, ENABLED, and V"
idMethod="none">
<column name="POSITION" type="VARCHAR" size="150" required="true"
primaryKey="true"/>
<column name="ORDERING" type="VARCHAR" size="240" required="false" />
<column name="SHORT_NAME" type="VARCHAR" size="10" required="false"/>
</table>
Hope that helps...
David
T E Schmitz wrote:
> Hello David, Thank you for your reply.
>
> How did you map Torque objects to VIEWs as these wouldn't have been in
> the xml schema?
>
> I haven't had any other suggestions but I noticed a posting on the
> Torque developers list: somebody has implemented explicit inner joins,
> left joins and right joins and offered to contribute his code.
>
> Best Regards,
>
> Tarlika Elisabeth Schmitz
>
>
> David Hainlin wrote:
>
>> Greetings, Don't know if you got your question answered, but the only
>> way I've done outer joins in Torque is to create a view and then map
>> a torque object to it. Has worked pretty well.
>> David
>>
>>>> -----Original Message-----
>>>> From: T E Schmitz <ma...@numerixtechnology.de>
>>>> To: Apache Torque Users List <to...@db.apache.org>
>>>> Date: Sun, 02 May 2004 17:43:21 +0100
>>>> Subject: left outer join
>>>>
>>>> I have 3 tables SECTION and BRAND and MODEL. SECTION is related to
>>>> BRAND and to MODEL via two foreign keys. I would like to select ALL
>>>> SECTIONs whether the FKs are null or not and fetch the BRAND and
>>>> MODEL attributes in one SQL statement. In other words I need a
>>>> double outer join:
>>>>
>>>> SELECT
>>>> SECTION.SECTION_PK,
>>>> SECTION.SECTION_NAME,
>>>> SECTION.BRAND_FK,
>>>> SECTION.MODEL_FK,
>>>> BRAND.BRAND_NAME
>>>> MODEL.MODEL_NAME
>>>> FROM SECTION
>>>> left outer join MODEL on MODEL_PK =MODEL_FK
>>>> left outer join BRAND on BRAND_PK =BRAND_FK
>>>>
>>>> I have seen some old discussion threads from 2002 suggesting that
>>>> Torque does not support outer joins. Is this still the case?
>>>>
>>>> If so, can anyone suggest an efficient work-around?
>>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: left outer join
Posted by T E Schmitz <ma...@numerixtechnology.de>.
Hello David, Thank you for your reply.
How did you map Torque objects to VIEWs as these wouldn't have been in
the xml schema?
I haven't had any other suggestions but I noticed a posting on the
Torque developers list: somebody has implemented explicit inner joins,
left joins and right joins and offered to contribute his code.
Best Regards,
Tarlika Elisabeth Schmitz
David Hainlin wrote:
> Greetings, Don't know if you got your question answered, but the only
> way I've done outer joins in Torque is to create a view and then map a
> torque object to it. Has worked pretty well.
> David
>
>>> -----Original Message-----
>>> From: T E Schmitz <ma...@numerixtechnology.de>
>>> To: Apache Torque Users List <to...@db.apache.org>
>>> Date: Sun, 02 May 2004 17:43:21 +0100
>>> Subject: left outer join
>>>
>>> I have 3 tables SECTION and BRAND and MODEL. SECTION is related to
>>> BRAND and to MODEL via two foreign keys. I would like to select ALL
>>> SECTIONs whether the FKs are null or not and fetch the BRAND and
>>> MODEL attributes in one SQL statement. In other words I need a double
>>> outer join:
>>>
>>> SELECT
>>> SECTION.SECTION_PK,
>>> SECTION.SECTION_NAME,
>>> SECTION.BRAND_FK,
>>> SECTION.MODEL_FK,
>>> BRAND.BRAND_NAME
>>> MODEL.MODEL_NAME
>>> FROM SECTION
>>> left outer join MODEL on MODEL_PK =MODEL_FK
>>> left outer join BRAND on BRAND_PK =BRAND_FK
>>>
>>> I have seen some old discussion threads from 2002 suggesting that
>>> Torque does not support outer joins. Is this still the case?
>>>
>>> If so, can anyone suggest an efficient work-around?
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: left outer join
Posted by David Hainlin <dh...@comcast.net>.
Greetings, Don't know if you got your question answered, but the only
way I've done outer joins in Torque is to create a view and then map a
torque object to it. Has worked pretty well.
David
T E Schmitz wrote:
> I'm sorry but I can't see how the problem you describe is related to
> my question. My Question was whether outer joins are supported or not
> and if not, what the work-around could be.
>
>
> Regards,
>
> Tarlika Elisabeth Schmitz
>
>
>
> Saravana Kannan wrote:
>
>> Hi,
>>
>> Note: The mail may seem unrelated if you dont read till the last line.
>>
>>
>> -----Original Message-----
>> From: T E Schmitz <ma...@numerixtechnology.de>
>> To: Apache Torque Users List <to...@db.apache.org>
>> Date: Sun, 02 May 2004 17:43:21 +0100
>> Subject: left outer join
>>
>> Hello,
>>
>> I have 3 tables SECTION and BRAND and MODEL. SECTION is related to
>> BRAND and to MODEL via two foreign keys. I would like to select ALL
>> SECTIONs whether the FKs are null or not and fetch the BRAND and
>> MODEL attributes in one SQL statement. In other words I need a double
>> outer join:
>>
>> SELECT
>> SECTION.SECTION_PK,
>> SECTION.SECTION_NAME,
>> SECTION.BRAND_FK,
>> SECTION.MODEL_FK,
>> BRAND.BRAND_NAME
>> MODEL.MODEL_NAME
>> FROM SECTION
>> left outer join MODEL on MODEL_PK =MODEL_FK
>> left outer join BRAND on BRAND_PK =BRAND_FK
>>
>> I have seen some old discussion threads from 2002 suggesting that
>> Torque does not support outer joins. Is this still the case?
>>
>> If so, can anyone suggest an efficient work-around? I'd rather not
>> fetch the other table's attributes in a loop.
>>
>> =======================================================================
>> 2nd Problem
>> -----------
>> Scenario as above but this time SECTION has two foreign key
>> relationships with the same table:
>>
>> SELECT
>> SECTION.SECTION_PK,
>> SECTION.SECTION_NAME,
>> SECTION.BRAND_1_SKIPPED_FK,
>> SECTION.BRAND_2_SKIPPED_FK,
>> BRAND.BRAND_NAME (1)
>> BRAND.BRAND_NAME (2)
>> FROM SECTION
>> left outer join BRAND on BRAND_PK =BRAND_1_SKIPPED_FK
>> left outer join BRAND on BRAND_PK =BRAND_2_SKIPPED_FK
>>
>> (the above obviously doens't work as SQL statement but ...)
>>
>> How would I specify the same output columns twice?
>> How can I implement this logic using Torque?
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: left outer join
Posted by T E Schmitz <ma...@numerixtechnology.de>.
I'm sorry but I can't see how the problem you describe is related to my
question. My Question was whether outer joins are supported or not and
if not, what the work-around could be.
Regards,
Tarlika Elisabeth Schmitz
Saravana Kannan wrote:
> Hi,
>
> Note: The mail may seem unrelated if you dont read till
> the last line.
>
>
> -----Original Message-----
> From: T E Schmitz <ma...@numerixtechnology.de>
> To: Apache Torque Users List <to...@db.apache.org>
> Date: Sun, 02 May 2004 17:43:21 +0100
> Subject: left outer join
>
> Hello,
>
> I have 3 tables SECTION and BRAND and MODEL. SECTION is related to BRAND
> and to MODEL via two foreign keys. I would like to select ALL SECTIONs
> whether the FKs are null or not and fetch the BRAND and MODEL attributes
> in one SQL statement. In other words I need a double outer join:
>
> SELECT
> SECTION.SECTION_PK,
> SECTION.SECTION_NAME,
> SECTION.BRAND_FK,
> SECTION.MODEL_FK,
> BRAND.BRAND_NAME
> MODEL.MODEL_NAME
> FROM SECTION
> left outer join MODEL on MODEL_PK =MODEL_FK
> left outer join BRAND on BRAND_PK =BRAND_FK
>
> I have seen some old discussion threads from 2002 suggesting that Torque
> does not support outer joins. Is this still the case?
>
> If so, can anyone suggest an efficient work-around? I'd rather not fetch
> the other table's attributes in a loop.
>
> =======================================================================
> 2nd Problem
> -----------
> Scenario as above but this time SECTION has two foreign key
> relationships with the same table:
>
> SELECT
> SECTION.SECTION_PK,
> SECTION.SECTION_NAME,
> SECTION.BRAND_1_SKIPPED_FK,
> SECTION.BRAND_2_SKIPPED_FK,
> BRAND.BRAND_NAME (1)
> BRAND.BRAND_NAME (2)
> FROM SECTION
> left outer join BRAND on BRAND_PK =BRAND_1_SKIPPED_FK
> left outer join BRAND on BRAND_PK =BRAND_2_SKIPPED_FK
>
> (the above obviously doens't work as SQL statement but ...)
>
> How would I specify the same output columns twice?
> How can I implement this logic using Torque?
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: left outer join
Posted by Saravana Kannan <sk...@engr.uky.edu>.
Hi,
Note: The mail may seem unrelated if you dont read till
the last line.
I have a more primitive problem. I have a Torque
Object called Answer which is related to AnswerGroup
and ExprNode. Answer can be either the child of
AnswerGroup OR a child of ExprNode.
So I do something like-
AnswerGroup ansgrp = new AnswerGroup();
AnswerGroup ans = new Answer();
..
(calling setter methods)
..
ansgrp.save();
ansgrp.addAnswer(ans);
ans.save();
And I get the Torque Exception which is the result of an Oracle constraint
error. The Oracle error was something of the sort-
"Foreign key 0 does not exist in ExprNode"
The question here is - Why is Torque/Village setting
ExprNodeFK to "0" when I never called
ExprNode.addAnswer() or ans.setExprNodeFK() ? I want
the ExprNodeFK to be null. I'm unable to create foreign
keys which are null. The relation to the parent
questions is - How did u manage creating a FK which is
null? As is the norm, my foreign keys are of type
INTEGER in the project-schema.xml.
Ofcourse I could create a dummy ExprNode by hand that
has PK = 0 but is not used anywhere. But I want to find
the better or proper way to take care of this problem.
Using ExprNode with PK=0 will be my last resort.
If the problem I mention is the way Torque works, then
I would like to suggest a solution to this problem as a
feature of newer releases of Torque. Thank you.
~Sarav
-----Original Message-----
From: T E Schmitz <ma...@numerixtechnology.de>
To: Apache Torque Users List <to...@db.apache.org>
Date: Sun, 02 May 2004 17:43:21 +0100
Subject: left outer join
Hello,
I have 3 tables SECTION and BRAND and MODEL. SECTION is related to BRAND
and to MODEL via two foreign keys. I would like to select ALL SECTIONs
whether the FKs are null or not and fetch the BRAND and MODEL attributes
in one SQL statement. In other words I need a double outer join:
SELECT
SECTION.SECTION_PK,
SECTION.SECTION_NAME,
SECTION.BRAND_FK,
SECTION.MODEL_FK,
BRAND.BRAND_NAME
MODEL.MODEL_NAME
FROM SECTION
left outer join MODEL on MODEL_PK =MODEL_FK
left outer join BRAND on BRAND_PK =BRAND_FK
I have seen some old discussion threads from 2002 suggesting that Torque
does not support outer joins. Is this still the case?
If so, can anyone suggest an efficient work-around? I'd rather not fetch
the other table's attributes in a loop.
=======================================================================
2nd Problem
-----------
Scenario as above but this time SECTION has two foreign key
relationships with the same table:
SELECT
SECTION.SECTION_PK,
SECTION.SECTION_NAME,
SECTION.BRAND_1_SKIPPED_FK,
SECTION.BRAND_2_SKIPPED_FK,
BRAND.BRAND_NAME (1)
BRAND.BRAND_NAME (2)
FROM SECTION
left outer join BRAND on BRAND_PK =BRAND_1_SKIPPED_FK
left outer join BRAND on BRAND_PK =BRAND_2_SKIPPED_FK
(the above obviously doens't work as SQL statement but ...)
How would I specify the same output columns twice?
How can I implement this logic using Torque?
--
Regards,
Tarlika Elisabeth Schmitz
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
--
There are 10 kinds of people in the world,
those who know binary and those who don't.
Quoting T E Schmitz <ma...@numerixtechnology.de>:
> Hello,
>
> I have 3 tables SECTION and BRAND and MODEL. SECTION is related to BRAND
> and to MODEL via two foreign keys. I would like to select ALL SECTIONs
> whether the FKs are null or not and fetch the BRAND and MODEL attributes
> in one SQL statement. In other words I need a double outer join:
>
> SELECT
> SECTION.SECTION_PK,
> SECTION.SECTION_NAME,
> SECTION.BRAND_FK,
> SECTION.MODEL_FK,
> BRAND.BRAND_NAME
> MODEL.MODEL_NAME
> FROM SECTION
> left outer join MODEL on MODEL_PK =MODEL_FK
> left outer join BRAND on BRAND_PK =BRAND_FK
>
> I have seen some old discussion threads from 2002 suggesting that Torque
> does not support outer joins. Is this still the case?
>
> If so, can anyone suggest an efficient work-around? I'd rather not fetch
> the other table's attributes in a loop.
>
> =======================================================================
> 2nd Problem
> -----------
> Scenario as above but this time SECTION has two foreign key
> relationships with the same table:
>
> SELECT
> SECTION.SECTION_PK,
> SECTION.SECTION_NAME,
> SECTION.BRAND_1_SKIPPED_FK,
> SECTION.BRAND_2_SKIPPED_FK,
> BRAND.BRAND_NAME (1)
> BRAND.BRAND_NAME (2)
> FROM SECTION
> left outer join BRAND on BRAND_PK =BRAND_1_SKIPPED_FK
> left outer join BRAND on BRAND_PK =BRAND_2_SKIPPED_FK
>
> (the above obviously doens't work as SQL statement but ...)
>
> How would I specify the same output columns twice?
> How can I implement this logic using Torque?
>
> --
>
>
> Regards,
>
> Tarlika Elisabeth Schmitz
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: left outer join
Posted by TES <Ta...@numerixtechnology.co.uk>.
Here's the correct SQL for the 2nd outer join example below.
SELECT
SECTION.SECTION_PK,
SECTION.SECTION_NAME,
SECTION.BRAND_1_FK,
SECTION.BRAND_2_FK,
b1.BRAND_NAME,
b2.BRAND_NAME
FROM SECTION
left outer join BRAND as b1 on b1.BRAND_PK =BRAND_1_FK
left outer join BRAND as b2 on b2.BRAND_PK =BRAND_2_FK
This leaves only the question "How can it be done using Torque?"
T E Schmitz wrote:
> Hello,
>
> I have 3 tables SECTION and BRAND and MODEL. SECTION is related to BRAND
> and to MODEL via two foreign keys. I would like to select ALL SECTIONs
> whether the FKs are null or not and fetch the BRAND and MODEL attributes
> in one SQL statement. In other words I need a double outer join:
>
> SELECT
> SECTION.SECTION_PK,
> SECTION.SECTION_NAME,
> SECTION.BRAND_FK,
> SECTION.MODEL_FK,
> BRAND.BRAND_NAME
> MODEL.MODEL_NAME
> FROM SECTION
> left outer join MODEL on MODEL_PK =MODEL_FK
> left outer join BRAND on BRAND_PK =BRAND_FK
>
> I have seen some old discussion threads from 2002 suggesting that Torque
> does not support outer joins. Is this still the case?
>
> If so, can anyone suggest an efficient work-around? I'd rather not fetch
> the other table's attributes in a loop.
>
> =======================================================================
> 2nd Problem
> -----------
> Scenario as above but this time SECTION has two foreign key
> relationships with the same table:
>
> SELECT
> SECTION.SECTION_PK,
> SECTION.SECTION_NAME,
> SECTION.BRAND_1_SKIPPED_FK,
> SECTION.BRAND_2_SKIPPED_FK,
> BRAND.BRAND_NAME (1)
> BRAND.BRAND_NAME (2)
> FROM SECTION
> left outer join BRAND on BRAND_PK =BRAND_1_SKIPPED_FK
> left outer join BRAND on BRAND_PK =BRAND_2_SKIPPED_FK
>
> (the above obviously doens't work as SQL statement but ...)
>
> How would I specify the same output columns twice?
> How can I implement this logic using Torque?
>
--
T.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org