How to rewrite this query using LEFT OUTER JOIN?

Hello Everyone!
I'm having problems with the INNER JOIN in the subquery. If it wasn't
for that, I'd be able to LEFT JOIN it and then check for nulls on the
right side, but I'm not sure where I'd stick the INNER JOIN.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
WHERE F.SHIP_CUSTOMER NOT IN
(SELECT
F.SHIP_CUSTOMER
FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
ON R.ORDER_NO = F.ORDER_NO
WHERE R.TXN_TYPE < 3
)
Thank you!
*** Sent via Developersdex http://www.examnotes.net ***SELECT COUNT(*)
FROM FND_DONATION_MASTER F
LEFT OUTER JOIN
(SELECT
F.SHIP_CUSTOMER
FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
ON R.ORDER_NO = F.ORDER_NO
WHERE R.TXN_TYPE < 3
) D
ON F.SHIP_CUSTOMER = D.SHIP_CUSTOMER
WHERE D.SHIP_CUSTOMER IS NULL
HTH,
John Scragg
"mEmENT0m0RI" wrote:

> Hello Everyone!
> I'm having problems with the INNER JOIN in the subquery. If it wasn't
> for that, I'd be able to LEFT JOIN it and then check for nulls on the
> right side, but I'm not sure where I'd stick the INNER JOIN.
>
> SELECT COUNT(*)
> FROM FND_DONATION_MASTER F
> WHERE F.SHIP_CUSTOMER NOT IN
> (SELECT
> F.SHIP_CUSTOMER
> FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
> ON R.ORDER_NO = F.ORDER_NO
> WHERE R.TXN_TYPE < 3
> )
> Thank you!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi,
the below query can be re-written with LEFT JOIN as below.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
LEFT JOIN
(SELECT
F.SHIP_CUSTOMER
FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
ON R.ORDER_NO = F.ORDER_NO
WHERE R.TXN_TYPE < 3
) Temp
ON F.SHIP_CUSTOMER = Temp.SHIP_CUSTOMER
WHERE F.SHIP_CUSTOMER IS NULL
Hope this helps.
Thanks,
Siva
"mEmENT0m0RI" wrote:

> Hello Everyone!
> I'm having problems with the INNER JOIN in the subquery. If it wasn't
> for that, I'd be able to LEFT JOIN it and then check for nulls on the
> right side, but I'm not sure where I'd stick the INNER JOIN.
>
> SELECT COUNT(*)
> FROM FND_DONATION_MASTER F
> WHERE F.SHIP_CUSTOMER NOT IN
> (SELECT
> F.SHIP_CUSTOMER
> FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
> ON R.ORDER_NO = F.ORDER_NO
> WHERE R.TXN_TYPE < 3
> )
> Thank you!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi
It is better to post DDL and example data along with your expected output
then your intensions will be cleared! See
http://www.aspfaq.com/etiquette.asp?id=5006 on how to do this.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
LEFT JOIN ( FND_DONATION_MASTER M
JOIN RECEIVABLE_TXN R ON R.ORDER_NO = F.ORDER_NO AND
R.TXN_TYPE < 3
) ON F.SHIP_CUSTOMER = M.SHIP_CUSTOMER
or possibly
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
LEFT JOIN RECEIVABLE_TXN R ON R.ORDER_NO = F.ORDER_NO AND R.TXN_TYPE < 3
WHERE R.ORDER_NO IS NULL
or you maybe:
SELECT COUNT(*)
FROM FND_DONATION_MASTER F
WHERE NOT EXISTS
(SELECT *
FROM RECEIVABLE_TXN R
WHERE R.ORDER_NO = F.ORDER_NO AND R.TXN_TYPE < 3
)
John
"mEmENT0m0RI" wrote:

> Hello Everyone!
> I'm having problems with the INNER JOIN in the subquery. If it wasn't
> for that, I'd be able to LEFT JOIN it and then check for nulls on the
> right side, but I'm not sure where I'd stick the INNER JOIN.
>
> SELECT COUNT(*)
> FROM FND_DONATION_MASTER F
> WHERE F.SHIP_CUSTOMER NOT IN
> (SELECT
> F.SHIP_CUSTOMER
> FROM FND_DONATION_MASTER F INNER JOIN RECEIVABLE_TXN R
> ON R.ORDER_NO = F.ORDER_NO
> WHERE R.TXN_TYPE < 3
> )
> Thank you!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||John,
Thank you for your reply! I tried your solution. It produces the right
result, however it runs even a little slower that the NOT IN variant.
I wonder, why this isn't working:
SELECT COUNT(*)
FROM FND_DONATION_MASTER F1
LEFT OUTER JOIN FND_DONATION_MASTER F2
ON F1.ship_customer = F2.ship_customer
INNER JOIN RECEIVABLE_TXN R2
ON R2.ORDER_NO = F2.ORDER_NO
AND R2.TXN_TYPE < 3
WHERE F2.ship_customer IS NULL
Is there a way in SQL to change the precedence of how multiple joins
apply?
Thank you,
Igor
*** Sent via Developersdex http://www.examnotes.net ***|||On Wed, 09 Nov 2005 08:57:56 -0800, mEmENT0m0RI wrote:

>John,
>Thank you for your reply! I tried your solution. It produces the right
>result, however it runs even a little slower that the NOT IN variant.
>I wonder, why this isn't working:
>SELECT COUNT(*)
>FROM FND_DONATION_MASTER F1
>LEFT OUTER JOIN FND_DONATION_MASTER F2
> ON F1.ship_customer = F2.ship_customer
>INNER JOIN RECEIVABLE_TXN R2
> ON R2.ORDER_NO = F2.ORDER_NO
> AND R2.TXN_TYPE < 3
>WHERE F2.ship_customer IS NULL
Hi Igor,
The LEFT OUTER JOIN is processed first. If any rows in F1 are not
matched against F2, they are retained in F1, with NULL values for the F2
columns. But then, the INNER JOIN is processed, that joins on
F2.ship_customer. For the retained rows, this is NULL, and no row in F3
will match. As a result, the INNER JOIN fails and the row is removed.
In the end, the INNER JOIN that uses columns from the outer table
transforms your outer join to a normal inner join.

>Is there a way in SQL to change the precedence of how multiple joins
>apply?
Two ways, even. Use parentheses, or re-order the joins
#1: Use parentheses.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F1
LEFT OUTER JOIN ( FND_DONATION_MASTER F2
INNER JOIN RECEIVABLE_TXN R2
ON R2.ORDER_NO = F2.ORDER_NO
AND R2.TXN_TYPE < 3 )
ON F1.ship_customer = F2.ship_customer
WHERE F2.ship_customer IS NULL
Note that the parentheses force me to move the ON clause for the OUTER
JOIN to the end.
Also note that the parentheses might even be removed in this query - the
location of the ON clauses (two JOIN clauses followed by two ON clauses)
is sufficient for SQL Server to figure out how this is meant. Humans are
less able to understand such a mess, though, so my advice is to leave
the parentheses in.
Even with the parentheses, the version above is hardlly readably. A
better alternative is:
#2: Reorder the joins.
SELECT COUNT(*)
FROM FND_DONATION_MASTER F2
INNER JOIN RECEIVABLE_TXN R2
ON R2.ORDER_NO = F2.ORDER_NO
AND R2.TXN_TYPE < 3
RIGHT OUTER JOIN FND_DONATION_MASTER F1
ON F1.ship_customer = F2.ship_customer
WHERE F2.ship_customer IS NULL
Note that the reversal of the F1 and F2 tables forces me to rename the
LEFT OUTER JOIN to a RIGHT OUTER JOIN (since the two tables have traded
placecs).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Guys,
I apologize for not replying to the thread for so long. I've been out of
the office for a couple of days.
I appreciate all your answers.
Hugo, special thanks to you; your answer is exactly what I was looking
for and something I couldn't figure out for a long time.
Thank you!
*** Sent via Developersdex http://www.examnotes.net ***