Re: Joining two tables without primary keys

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Sat, 21 Jul 2001 21:28:27 GMT
Message-ID: <9i14t3$r0$1@s1.read.news.oleane.net>

"Rajesh Jayaprakash" <rajeshj_at_asdc.co.in> a écrit dans le message news: 3f97587f.0107042236.64368467_at_posting.google.com...
> Hello everybody,
>
> I have two tables (TAB1 and TAB2).
>
> TAB1 has records as follows:
>
> R1 R2
> --- -------
> 1 2
> 1 2
> 3 4
> 1 2
>
> TAB2 has the following records:
>
> R1 R2 R3
> --- ------- -----
> 1 2 Y
> 1 2 Y
> 3 4 N
> 3 4 N
>
> There are no primary keys for either of the tables as duplicates are
> possible.
> When I join the two tables using R1 and R2, I get a cartesian product.
>
> But is it possible to get a result set which contains the number of
> rows exactly as in TAB1, i.e
>
> R1 R2 R3
> --- ------- -----
> 1 2 Y
> 1 2 Y
> 1 2 Y
> 3 4 N
>
> Is it possible in any way to filter the result set so that the number
> of rows is same as that in TAB1?
>
> One solution is to use the rowid of TAB1, but I would like to know if
> there is any other way.
>
> Any help will be appreciated.
>
> Thanks and regards,
>
> Rajesh Jayaprakash

You can do something like:

v815> create table tab1 (R1 number, r2 number);
v815> insert into tab1 values (1,2);
v815> insert into tab1 values (1,2);
v815> insert into tab1 values (3,4);
v815> insert into tab1 values (1,2);
v815> create table tab2 (R1 number, r2 number, r3 varchar2(10));
v815> insert into tab2 values (1,2,'Y');
v815> insert into tab2 values (1,2,'Y');
v815> insert into tab2 values (3,4,'N');
v815> insert into tab2 values (3,4,'N');
v815> commit;
v815> select a.*, b.r3

  2 from (select distinct r1, r2, r3 from tab2) b, tab1 a   3 where b.r1 = a.r1 and b.r2 = a.r2;

        R1 R2 R3
---------- ---------- ----------

         1          2 Y
         1          2 Y
         1          2 Y
         3          4 N

4 rows selected.

If you have some rows in tab1 that have no associated row in tab2, you have to use outer join:

v815> insert into tab1 values (5,6);
v815> commit;
v815> select a.*, b.r3

  2 from (select distinct r1, r2, r3 from tab2) b, tab1 a   3 where b.r1 (+) = a.r1 and b.r2 (+) = a.r2;

        R1 R2 R3
---------- ---------- ----------

         1          2 Y
         1          2 Y
         1          2 Y
         3          4 N
         5          6

5 rows selected.

--
Have a nice day
Michel

Received on Sat Jul 21 2001 - 16:28:27 CDT