Joining Tables with no duplicate value

[TekMem]

I have three tables and I want to join them so that no value is repeated in the columns.

My tables are
1. Cir
Cirno Address
C111 50 Yonge St
C112 15 Victoria Park

2. Appl
Applno Cirno Address
A500 C112 15 Victoria Park
A501 C112 60 Flower Town
A502 90 Law Av.

3. Per
Perno Cirno Applno
P888
P889
P890 C112 A500
P891 C112 A501
P892 C112 A501
P893 C112 A501
P894 A502

I want to see results
Cirno Applno Per
C111 NA NA
C112 A500 P890
A501 P891
P892
P893
NA A502 P894

NA is not data available.

Any Suggestions? Thanks in Ad.

 

[LKBrwnDBA]

Show us how far have you got with this homework assignment.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[TekMem]

I have created smaller tables for this thread with diff name but my original select statement is something like this

select a.section_name, a.varchar_255_19,a.varchar_255_17 as appl, p.document_type, p.varchar_255_17 from appl_env_main a ,permit_env_main p
where a.varchar_255_17=p.document_code(+)
UNION
SELECT section_name, varchar_255_19,document_code as appl, document_type, varchar_255_17 FROM PERMIT_ENV_MAIN a WHERE VARCHAR_255_17 NOT LIKE 'THA%'
order by appl

Result is not what I want to see

 

[SantaMufasa]

TekMem,

Besides LK's concern that this might be a homework assignment for a class (with which we at Tek-Tips are not allowed to assist), your output from your data are a bit puzzling to me:[ul][li]Why do you want these results:

C111    NA      NA

...which derive from your "CIR" table data:

[tt]"C111 50 Yonge St"[/tt]

but you don't want results deriving from your data:

[tt]"C112 15 Victoria Park"[/tt]

[/li][li]To achieve the results for which you are asking, there is no need to access or consider the "APPL" table, right?[/li][/ul]So, after you post the code you have developed so far (and also posted some assurance that this is not a classroom assignment), then please address my concerns, above.


At that point, we can continue to address your question(s).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[TekMem]

select a.applno, p.perno,p.cirno from appl a ,per p
where a.applno=p.applno
UNION
SELECT '', perno,'' FROM PER where applno is null
UNION
SELECT applno, '','' FROM appl where cirno is null

 

[SantaMufasa]

Sorry, I didn't see your response, TekMem, before I posted my response.

Since you posted your code, I believe we can be comfortable that this is not a classroom assignment.

Here is what I have, so far:

SQL> select * from cir;

CIRNO      ADDRESS
---------- --------------------
C111       50 Yonge St
C112       15 Victoria Park

2 rows selected.

SQL> select * from appl;

APPLNO     CIRNO      ADDRESS
---------- ---------- ----------------
A500       C112       15 Victoria Park
A501       C112       60 Flower Town
A502                  90 Law Av.

3 rows selected.

SQL> select * from per;

PERNO      CIRNO      APPLNO
---------- ---------- ----------
P888
P889
P890       C112       A500
P891       C112       A501
P892       C112       A501
P893       C112       A501
P894                  A502

break on cirno on applno
select nvl(cirno,'NA') cirno, applno, perno from per
 where applno is not null
union
select cirno, 'NA' applno, 'NA' per from cir
/

CIRNO      APPLNO     PERNO
---------- ---------- --------
C111       NA         NA
C112       A500       P890
           A501       P891
                      P892
                      P893
           NA         NA       <-- This is the "C112...15 Victoria Park" row that I mentioned in my earlier post.
NA         A502       P894

7 rows selected.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Sorry...another cross post on my part, TekMem.

Here is what your code produces from the rows you posted:

select  a.applno,  p.perno,p.cirno  from appl a ,per  p
where a.applno=p.applno
UNION
SELECT '', perno,'' FROM PER where applno is null
UNION
SELECT applno, '','' FROM appl where cirno is null

APPLNO     PERNO      CIRNO
---------- ---------- -----
A500       P890       C112
A501       P891       C112
A501       P892       C112
A501       P893       C112
A502       P894
A502
           P888
           P889

8 rows selected.

That looks rather different from the output you wanted, right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”