SQL question

[Alex]

Hi ALL

I've two tables and am trying to apply an OUTER JOIN on multiple fields.

Table A has some rows as:

<Table A>
--- ---
1 a
1 b
2 b
3 a
3 c

Table B is missing some of the rows.

<Table B>
--- ---
1 a
2 b
3 c


Looking for only those rows Table B is missing:

----------
1 b NULL
3 a NULL


The following is not working:

select t1.number, t1.letter, t2.number, t2.letter
from TableA t1, TableB t2
where t1.number = (+)t2.number
and t1.letter = (+)t2.letter

[Hans Forbrich]

The OUTER JOIN topic is rather significant and there are some
limitations, such as FULL OUTER JOIN is not intuitively obvious.

Please consider reading the relevant material: http://docs.oracle.com >
doc for your version (this is definitely version dependant) and search
on 'Outer Join'.

Also, when posting such questions, please indicate the message from the
server. A blunt 'not working' could be a typo, a syntax error, or
possibly it's running to completion with results other than unexpected.

[az5]

select a.col1,a.col2, null col3 from a
where not exists (select null from b where b.col1=a.col1 and b.col2=a.col2)

regards az5

"Alex" <alexsm@eudoramail.com> wrote in message
news:29ff93bc.0311041324.2a524f3b@posting.google.com... Hi ALL I've two tables and am trying to apply an OUTER JOIN on multiple fields. Table A has some rows as: <Table A> --- --- 1 a 1 b 2 b 3 a 3 c Table B is missing some of the rows. <Table B> --- --- 1 a 2 b 3 c Looking for only those rows Table B is missing: ---------- 1 b NULL 3 a NULL The following is not working: select t1.number, t1.letter, t2.number, t2.letter from TableA t1, TableB t2 where t1.number = (+)t2.number and t1.letter = (+)t2.letter

[Merci]

select a.num, a.let, decode(b.num,null,'NULL')
from a, b
where b.num(+) = a.num
and b.let(+) = a.let
and b.num is null


"az5" <az5@telus.net> wrote in message news:KfWpb.9898$6A4.9774@edtnps84... select a.col1,a.col2, null col3 from a where not exists (select null from b where b.col1=a.col1 and
b.col2=a.col2) regards az5 "Alex" <alexsm@eudoramail.com> wrote in message news:29ff93bc.0311041324.2a524f3b@posting.google.com... Hi ALL I've two tables and am trying to apply an OUTER JOIN on multiple fields. Table A has some rows as: <Table A> --- --- 1 a 1 b 2 b 3 a 3 c Table B is missing some of the rows. <Table B> --- --- 1 a 2 b 3 c Looking for only those rows Table B is missing: ---------- 1 b NULL 3 a NULL The following is not working: select t1.number, t1.letter, t2.number, t2.letter from TableA t1, TableB t2 where t1.number = (+)t2.number and t1.letter = (+)t2.letter

[Mr E Guest]

Why not...

SELECT num,
let
FROM a
MINUS
SELECT num,
let
FROM b;

"Merci" <msmith@whi.org> wrote in message news:<bobhc4$s1k$1@lists.fhcrc.org>... select a.num, a.let, decode(b.num,null,'NULL') from a, b where b.num(+) = a.num and b.let(+) = a.let and b.num is null "az5" <az5@telus.net> wrote in message news:KfWpb.9898$6A4.9774@edtnps84... select a.col1,a.col2, null col3 from a where not exists (select null from b where b.col1=a.col1 and b.col2=a.col2) regards az5 "Alex" <alexsm@eudoramail.com> wrote in message news:29ff93bc.0311041324.2a524f3b@posting.google.com... Hi ALL I've two tables and am trying to apply an OUTER JOIN on multiple fields. Table A has some rows as: <Table A> --- --- 1 a 1 b 2 b 3 a 3 c Table B is missing some of the rows. <Table B> --- --- 1 a 2 b 3 c Looking for only those rows Table B is missing: ---------- 1 b NULL 3 a NULL The following is not working: select t1.number, t1.letter, t2.number, t2.letter from TableA t1, TableB t2 where t1.number = (+)t2.number and t1.letter = (+)t2.letter