Message-ID: <42020265.2000708@archonet.com>
Date: Thu, 03 Feb 2005 10:52:21 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: imam <imam@effigent.net>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: problem while converting sybase quries to postgres
References: <003901c5082d$bac76f30$1301a8c0@eipl.com>
	<4201EFF5.5000006@archonet.com>
	<050f01c509d9$036d86f0$1301a8c0@eipl.com>
In-Reply-To: <050f01c509d9$036d86f0$1301a8c0@eipl.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Please cc: the list when you reply to me - that way others can help too.

imam wrote:
>>If this is a left outer join, you could do something like:
>>SELECT
>>  ...
>>FROM
>>  pe_pop_sr_posted_tran_head t1
>>LEFT JOIN
>>  pe_pop_po_header t2
>>ON
>>  t1.po_header_pk = t2.po_header_pk
>>LEFT JOIN
>>  pe_pop_sr_posted_tran_det t3
>>ON
>>  t2.po_header_pk = t3.po_header_pk
>>  AND t1.sr_tran_head_pk = t3.sr_tran_head_pk
>>
>>But you'll want to test it because I'm not clear what your query is doing.
>>--
>>   Richard Huxton
>>   Archonet Ltd
> 
> 
> Thanking for you reply the problem is that i have a "or" condition and left
> outer join between two table.A po_header_table is comman in both the join as
> given below
> t1.PO_HEADER_PK *= t2.PO_HEADER_PK or t3.PO_HEADER_PK *=
>>>t2.PO_HEADER_PK)

Ah! Hadn't spotted that. Would this be what you're after?

SELECT ... FROM
(
SELECT
   t1.po_header_pk AS t1_phpk
   t3.po_header_pk AS t3_phpk
   ...
FROM
   t1, t3
WHERE
   t1.sr_tran_head_pk = t3.sr_tran_head_pk
)
AS first
LEFT JOIN
t2
ON (
   first.t1_phpk = t2.po_header_pk
   OR first.t3_phpk = t2.po_header_pk
)

Not sure what happens if t1_phpk and t3_phpk both match different rows 
in t2 or whether that's what you want.
--
   Richard Huxton
   Archonet Ltd