Re: SQL question

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 4 Nov 2003 22:00:56 GMT
Message-ID: <bo97io$1blo5m$1@ID-82536.news.uni-berlin.de>

> 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

First: the (+) is on the right side of the expression, so your statement should read .... t2.number(+) instead of (+)ts.number.

Then: number is a reserved word in Oracle and should not be used for column names, at least not in my opinion.

Here's a working example:

drop table t_a;
drop table t_b;

create table t_a (
  u number,
  v varchar2(5)
);

create table t_b (
  s number,
  t varchar2(5)
);

insert into t_a values(1,'a');
insert into t_a values(1,'b');
insert into t_a values(1,'b');
insert into t_a values(3,'a');
insert into t_a values(3,'c');

insert into t_b values(1,'a');

insert into t_b values(2,'b');
insert into t_b values(3,'c');

select

  t_a.u, 
  t_a.v, 
  t_b.s, 
  t_b.t

from
  t_a,
  t_b
where

  t_a.u = t_b.s (+) and    
  t_a.v = t_b.t (+) and
  t_b.s is null;

Maybe, you want to modify the statement to return distinct values. If so, use  

   select distinct

instead of

   select

A more elegant solution (imho) is here:

select u, v from t_a
  minus
select s,t from t_b;

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Tue Nov 04 2003 - 16:00:56 CST