Thread: left join conditon causes error

Query

SELECT
   bilkaib.*
FROM prpalk,
(SELECT TRUE AS db, 1 AS sign UNION SELECT FALSE,-1 ) role,konto
destkonto,bilkaib
  LEFT JOIN kurss sihrkurs ON CASE WHEN '00' IN(cr,db) THEN
bilkaib.kuupaev-1 ELSE bilkaib.kuupaev END
                          =sihrkurs.kuupaev AND sihrkurs.raha=destkonto.raha
WHERE CASE WHEN role.db THEN bilkaib.db ELSE bilkaib.cr
END=destkonto.kontonr
AND (destkonto.raha=prpalk.pohiraha OR sihrkurs.raha=destkonto.raha )

  AND bilkaib.summa<>0 AND destkonto.kontonr<>'00'
  AND (bilkaib.kuupaev='2008-01-01 00:00:00.000000' OR (bilkaib.cr!='00' AND
bilkaib.db!='00'))

 AND destkonto.kontonr LIKE '111%' ESCAPE '!'
 AND bilkaib.kuupaev BETWEEN '2008-01-01 00:00:00.000000' AND '2008-01-03
00:00:00.000000'

causes error

ERROR:  invalid reference to FROM-clause entry for table "destkonto"
LINE 7: ...              =sihrkurs.kuupaev AND sihrkurs.raha=destkonto....
                                                             ^
HINT:  There is an entry for table "destkonto", but it cannot be referenced
from this part of the query.


I tried to remove AND sihrkurs.raha=destkonto.raha

and move it to where clause:

AND (destkonto.raha=prpalk.pohiraha OR sihrkurs.raha=destkonto.raha )

from left join but in this case query returns duplicate rows from bilkaib
table.

Why destkonto table cannot used but other tables in FROM list can be used in
join condition ?
How to use such left join ?

Andrus.

2008/10/8 Andrus <kobruleht2@hot.ee>:
> Query

Query deleted for brevity

> ERROR:  invalid reference to FROM-clause entry for table "destkonto"
> LINE 7: ...              =sihrkurs.kuupaev AND sihrkurs.raha=destkonto....
>                                                            ^
> HINT:  There is an entry for table "destkonto", but it cannot be referenced
> from this part of the query.

It's a question of precedence.

select * from a,b join c ...

is execute in the order of

b join c

then a join that result.  By the way, this is how the sql spec says to do it.

So, you can't join b to c based on anything from a, because a hasn't
been joined yet.  I would recommend using explicit join syntax only
and see how that works for you.