pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLet join syntax
5+ messages / 3 participants
[nested] [flat]
* Let join syntax
@ 2003-06-16 10:34 Nicolas JOUANIN <n.jouanin@regie-france.com>
0 siblings, 2 replies; 5+ messages in thread
From: Nicolas JOUANIN @ 2003-06-16 10:34 UTC (permalink / raw)
To: pgsql-sql
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 ,
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 )
Unfortunatelly, postgres returns me the following error :
Error: ERROR: Relation "x0" does not exist (State:S1000, Native Code: 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_id AND 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
---------------------------------------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Let join syntax
@ 2003-06-16 10:57 Manfred Koizar <mkoi-pg@aon.at>
parent: Nicolas JOUANIN <n.jouanin@regie-france.com>
1 sibling, 1 reply; 5+ messages in thread
From: Manfred Koizar @ 2003-06-16 10:57 UTC (permalink / raw)
To: Nicolas JOUANIN <n.jouanin@regie-france.com>; +Cc: pgsql-sql
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
FROM
coh x0 INNER JOIN cpy x1 ON [...] INNER JOIN bra x2 ON [...]
INNER JOIN cur x3 ON [...] INNER JOIN tad x4 ON [...]
LEFT JOIN 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 ...
Servus
Manfred
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Let join syntax
@ 2003-06-16 14:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2003-06-16 14:31 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: Nicolas JOUANIN <n.jouanin@regie-france.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Let join syntax
@ 2003-06-16 14:39 Manfred Koizar <mkoi-pg@aon.at>
parent: Nicolas JOUANIN <n.jouanin@regie-france.com>
1 sibling, 0 replies; 5+ messages in thread
From: Manfred Koizar @ 2003-06-16 14:39 UTC (permalink / raw)
To: Nicolas JOUANIN <n.jouanin@regie-france.com>; +Cc: pgsql-sql
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_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
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...
Servus
Manfred
^ permalink raw reply [nested|flat] 5+ messages in thread
* Let join syntax
@ 2003-06-16 16:41 Nicolas JOUANIN <n.jouanin@regie-france.com>
0 siblings, 0 replies; 5+ messages in thread
From: Nicolas JOUANIN @ 2003-06-16 16:41 UTC (permalink / raw)
To: pgsql-sql
Hi,
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
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
worked correctly
Thanks.
> -----Message d'origine-----
> De : Manfred Koizar [mailto:mkoi-pg@aon.at]
> Envoye : lundi 16 juin 2003 16:40
> A : Nicolas JOUANIN
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Let join syntax
>
>
> 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_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
> 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...
>
> Servus
> Manfred
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-06-16 16:41 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-16 10:34 Let join syntax Nicolas JOUANIN <n.jouanin@regie-france.com>
2003-06-16 10:57 ` Manfred Koizar <mkoi-pg@aon.at>
2003-06-16 14:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-16 14:39 ` Manfred Koizar <mkoi-pg@aon.at>
2003-06-16 16:41 Let join syntax Nicolas JOUANIN <n.jouanin@regie-france.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox