Hello,

I want to do a left outer join on an table which has a condition to be found in another table. How can I put this in a query?

The following code shows what I want to do, but doesn't work. It generates an error #11949.

select
 P.ID_POSITIE, sum(PK.BEDRAG) as BEDRAG
from
 POSITIES P
left outer join
 POSITIE_KOSTEN PK
on
 P.ID_POSITIE = PK.ID_POSITIE
 and Upper(PK.ID_KOSTENTYPE) in ( select Upper(ID_KOSTENTYPE) from POSITIE_KOSTEN_TYPES where Upper(BEREKENING) = Upper ('B') )
where
 P.ID_POSITIE between 810000 and 812000
group by
 ID_POSITIE

I know there is a solution by first combining the second and third table [POSITIE_KOSTEN and POSITIE_KOSTEN_TYPES] and then performing the left outer
join, but that is way too slow..

Does anyone know how to do this properly?

Thanks in advance,

Regards, Ries van der Velden

Ries


Does moving the second join condition to the WHERE clause work? eg

select
P.ID_POSITIE, sum(PK.BEDRAG) as BEDRAG
from
POSITIES P
left outer join
POSITIE_KOSTEN PK
on
P.ID_POSITIE = PK.ID_POSITIE
where
P.ID_POSITIE between 810000 and 812000
AND
Upper(PK.ID_KOSTENTYPE) in ( select Upper(ID_KOSTENTYPE) from POSITIE_KOSTEN_TYPES where Upper(BEREKENING) = Upper ('B') )
group by
ID_POSITIE

Roy Lambert [Team Elevate]

Ries,

<< The following code shows what I want to do, but doesn't work. It
generates an error #11949. >>

In the interest of saving time, what is the exact error message that you're
getting ?

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

>In the interest of saving time, what is the exact error message that you're
>getting ?

DBISAM Engine Error # 11949 SQL parsing error - Invalid expression seelct found in SELECT SQL statement at line 5 column 36

Roy: although I do not really understand why, your solution is working..! As far as I understand, the POSITIE_KOSTEN table [PK] should not be valid in the
where clause, as the where clause implies to the 'from POSITIES' part..

Could you please explain why this works?

Regards, Ries

Ries,

<< DBISAM Engine Error # 11949 SQL parsing error - Invalid expression seelct
found in SELECT SQL statement at line 5 column 36 >>

Okay, the only option is to put the IN condition in the WHERE clause
instead.  However, please note that this may cause slightly different
results because the IN condition will not be used to generate NULL values
for any PK rows that do not match a P row on the P.ID_POSITIE =
PK.ID_POSITIE condition.  When a condition is in a LOJ clause (during-join
clause), then it is NULL-generating.  When a condition is in a WHERE clause
(after-join clause), then it is applied to the NULL values that are possibly
generated from the LOJ condition(s).

<< Roy: although I do not really understand why, your solution is working..!
As far as I understand, the POSITIE_KOSTEN table [PK] should not be valid in
the where clause, as the where clause implies to the 'from POSITIES' part..
>>

No, the WHERE clause conditions can be applied to any of the tables in the
FROM clause.

--
Tim Young
Elevate Software
www.elevatesoft.com