Thread: Let join syntax
Hi, I've a problem with the following query. I want to left join table sec (x5) with x0, and x4. I try the following query : select x0.coh_agent ,x0.bra_id ,x0.dpr_id ,x0.usr_id ,x0.csc_id ,x0.spp_id ,x0.csc_id_inv ,x0.coh_doc_inv ,x0.coh_d_inv, x0.coh_process ,x0.coh_doc_main ,x0.coh_status ,x0.coh_total_local ,x0.coh_basis_local ,x0.coh_cost_local,x0.coh_profit_local ,x0.coh_over_local ,x0.coh_com_earned ,x0.coh_com_adjust ,x0.coh_com_held ,x0.coh_com_cancel,x0.coh_com_topay ,x0.coh_d_paid ,x0.coh_matchname ,x0.coh_cscmatch ,x0.coh_com_paid ,x3.cur_dec_nb ,x3.cur_location ,x3.cur_negative ,x3.cur_dec_char ,x3.cur_group_char ,x5.sec_id ,x1.cpy_id ,x1.cpy_cr_tr ,x1.cpy_cr_tg,x1.cpy_cr_tb ,x2.bra_screen ,x2.bra_id ,x2.lng_id ,x4.tad_cpy_name from coh x0 , cpy x1 ,bra x2 , curx3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, dpr x6 where ((((((((((x0.cpy_id = x1.cpy_id )AND (x0.bra_id = x2.bra_id ) ) AND (x0.cur_id = x3.cur_id ) ) AND (x0.dpr_id = x6.dpr_id ) ) AND (x2.tad_id = x4.tad_id) ) AND (x5.thr_id = x4.thr_id ) ) AND (x2.bra_id = x6.bra_id ) ) AND (x5.usr_id = x0.usr_id ) ) AND (x5.bra_id= x0.bra_id ) ) AND (x5.dpr_id = x0.dpr_id ) Unfortunatelly, postgres returns me the following error :Error: ERROR: Relation "x0" does not exist (State:S1000, NativeCode: 7) I tried to modify the FROM sentence by:FROM (coh x0 , cpy x1 ,bra x2 , cur x3 ,tad x4 ) LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_idAND x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, but I get a syntax error , may be due to parentheses. Does anyone knows how to write this query correctly ? Regards, Nicolas. --------------------------------------------------------------- Nicolas JOUANIN - SA REGIE FRANCE Village Informatique BP 3002 17030 La Rochelle CEDEX Tel: 05 46 44 75 76 Fax: 05 46 45 34 17 email: n.jouanin@regie-france.com Web : www.regie-france.com ---------------------------------------------------------------
On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN" <n.jouanin@regie-france.com> wrote: >from > coh x0 , cpy x1 ,bra x2 , > cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND >x5.usr_id=x0.usr_id AND [...] > >Unfortunatelly, postgres returns me the following error : > Error: ERROR: Relation "x0" does not exist Yes, because the LEFT OUTER JOIN only sees x4 and x5. I have not analysed all your join conditions, but FROMcoh x0 INNER JOIN cpy x1 ON [...] INNER JOIN bra x2 ON [...]INNER JOIN cur x3 ON [...] INNER JOIN tad x4 ON [...]LEFTJOIN sec x5 ON [...] might work. If there is nothing to join x1, x2, x3, x4 on, you could try to put x5 to the front and use RIGHT JOINs ... ServusManfred
Manfred Koizar <mkoi-pg@aon.at> writes:
> On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN"
> <n.jouanin@regie-france.com> wrote:
>> from
>> coh x0 , cpy x1 ,bra x2 ,
>> cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND
>> x5.usr_id=x0.usr_id AND [...]
>>
>> Unfortunatelly, postgres returns me the following error :
>> Error: ERROR: Relation "x0" does not exist
> Yes, because the LEFT OUTER JOIN only sees x4 and x5.
The way that query is constructed, you've put all of the join conditions
into the LEFT JOIN's constraint, which will not do what you want even if
there weren't a syntactic failure. As an example, consider the
difference between
(a cross join b) left join c on (a.a1 = b.b1 and a.a2 = c.c2)
(a join b on a.a1 = b.b1) left join c on (a.a2 = c.c2)
The former is almost surely wrong: it will produce a row for *every*
combination in the cross product of a and b. Rows where a1 != b1 will
still be emitted --- but the c columns will be nulled out, even if
a2 = c2 is true, because the left join condition is false at such rows.
The second one is probably what was meant, instead.
In short, the reason why the SQL spec syntax for JOIN is the way it is
is that there's a big difference between conditions you put in an outer
join's ON clause and those you put elsewhere.
regards, tom lane
On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN"
<n.jouanin@regie-france.com> wrote:
>from
> coh x0 , cpy x1 ,bra x2 ,
> cur x3 ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND
>x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id,
> dpr x6 where ((((((((((x0.cpy_id = x1.cpy_id ) AND (x0.bra_id
> = x2.bra_id ) ) AND (x0.cur_id = x3.cur_id ) ) AND (x0.dpr_id
> = x6.dpr_id ) ) AND (x2.tad_id = x4.tad_id ) ) AND (x5.thr_id
> = x4.thr_id ) ) AND (x2.bra_id = x6.bra_id ) ) AND (x5.usr_id
> = x0.usr_id ) ) AND (x5.bra_id = x0.bra_id ) ) AND (x5.dpr_id
> = x0.dpr_id )
Nicolas, sometimes reformatting a query helps a lot:
FROM coh x0 , cpy x1 ,bra x2 , cur x3 , tad x4 LEFT OUTER JOIN sec x5 ON x5.thr_id = x4.thr_id AND x5.usr_id =
x0.usr_idAND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id, dpr x6
WHERE x0.cpy_id = x1.cpy_id AND x0.bra_id = x2.bra_id AND x0.cur_id = x3.cur_id AND x0.dpr_id = x6.dpr_id AND x2.tad_id
=x4.tad_id AND x2.bra_id = x6.bra_id AND x5.thr_id = x4.thr_id AND x5.usr_id = x0.usr_id AND x5.bra_id = x0.bra_id AND
x5.dpr_id= x0.dpr_id
First note that the last four lines duplicate the ON conditions thus
effectively turning the OUTER JOIN into an INNER JOIN. As I suspect
that that was not your intention, simply omit those four conditions
from the WHERE clause.
Now inserting INNER JOIN where the syntax forces us to do so leads to
(completely untested):
FROM coh x0 INNER JOIN bra x2 ON x0.bra_id = x2.bra_id INNER JOIN tad x4 ON x2.tad_id = x4.tad_id LEFT JOIN sec x5 ON
x5.thr_id= x4.thr_id AND x5.usr_id = x0.usr_id AND x5.bra_id = x0.bra_id AND
x5.dpr_id = x0.dpr_id, cpy x1, cur x3, dpr x6
WHERE x0.cpy_id = x1.cpy_id AND x0.cur_id = x3.cur_id AND x0.dpr_id = x6.dpr_id AND x2.bra_id = x6.bra_id
You might feel like replacing the remaining commas in the FROM clause
and the corresponding WHERE conditions with semantically equivalent
INNER JOINs. But this limits the freedom of the planner which may be
a good or a bad thing...
ServusManfred