db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From TES <Tarl...@numerixtechnology.co.uk>
Subject Re: left outer join
Date Sun, 02 May 2004 17:12:23 GMT
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


Mime
View raw message