SELECT outer join

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tpthai@pepco.com - 30 Mar 2005 15:42 GMT

Hi,
I appreciate if anyone can help me out, please. I have two tables;
TableA and TableB. When the user enters the account, I match it
against TableA.col1. Then I get the TableA.col2 from TableA and match
against TableB.col1. If not found, I still want the record to show.
My SQL only works if record is found on both tables. Here is my
current SQL:

select a.col1, a.col2, b.col2
from TableA a LEFT OUTER JOIN TableB b
where a.col1 = '456'
and a.col2 = b.col1

examples:
TableA
col1 col2
123 111
456 222
789 333

TableB
col1 col2
111 94.95
444 100.00

Desired result:
TableA.col1 TableA.col2 TableB.col2

456 222 0.00

Thanks in advance.
Teresa

Reply to this Message

Ugrasena - 30 Mar 2005 18:14 GMT

If a.col2 = b.col1 then try this Statement

select a.col1, a.col2,
(CASE WHEN b.col2 is null THEN 0 ELSE b.col2 END)
from TableA a LEFT OUTER JOIN TableB b On a.col2 = b.col1
where a.col1 = '456' and a.col2 = b.col1

Reply to this Message

Art S. Kagel - 31 Mar 2005 00:19 GMT

> Hi,

Make that:

select a.col1, a.col2, b.col2
from TableA a LEFT OUTER JOIN TableB b
ON a.col1 = '456'
and a.col2 = b.col1;

With the comparisons in the WHERE clause instead of the ON clause they are
post-join conditions. The optimizer may work it all out, but you're asking
for a cartesian product then filtering the results. This way, with the
filter and join condition as pre-join conditions, the outer join only
creates the desired rows in the first place.

Art S. Kagel

> I appreciate if anyone can help me out, please. I have two tables;
> TableA and TableB. When the user enters the account, I match it
[quoted text clipped - 27 lines]
> Thanks in advance.
> Teresa

Reply to this Message