I'm having some trouble joining these 3 tables.
Table1
OWNER TYPE
O1 T1
O1 T2
O2 T1
Table2
OWNER PERSON
O1 P1
O1 P2
O2 P1
Table3
TYPE PERSON
T1 P1
T2 P2
T1 P2
I want to do a join on all 3 tables so my output would be:
FinalTable
OWNER TYPE PERSON
O1 T1 P1
O1 T2 P2
O2 T1 P2
O1 T1 P2
I'm basically demonstrating how 5NF removes spurious rows. When I join any 2 combinations of the table I get extra data (which is what I expect).
I'm having difficulty joining the third table though, I've never had to work with joining all 3.
This is my query so far:
select r1.*, r2.PERSON from table1 r1, table2 r2, table3 r3
where r1.OWNER = r2.OWNER and r2.PERSON = r3.PERSON
My output returns 8 rows and some are duplicated. I've tried playing around with the query but it only increases the rows further.
My query is based on an ORACLE APEX tutorial book I have but it isn't joining 3 tables that are laid out like mine.
How do I join these 3 tables to remove spurious rows that I get after joining 2?
I put 1 2 3 to every column name to see which table they were under before. First 3 Query are your approaches and 1 of the relations is ignored in each. 4th approach all 3 relations are put in.
where t1.owner1=t2.owner2 AND t1.type1=t3.type3 ignoring t2.person2-t3.person3
| owner1 | type1 | owner2 | person2 | type3 | person3 |
|--------|-------|--------|---------|-------|---------|
| O1 | T1 | O1 | P1 | T1 | P1 |
| O1 | T1 | O1 | P1 | T1 | P2 |different ppl
| O1 | T1 | O1 | P2 | T1 | P1 |different ppl
| O1 | T1 | O1 | P2 | T1 | P2 |
| O1 | T2 | O1 | P1 | T2 | P2 |different ppl
| O1 | T2 | O1 | P2 | T2 | P2 |
| O2 | T1 | O2 | P1 | T1 | P1 |
| O2 | T1 | O2 | P1 | T1 | P2 |different ppl
where t2.owner1=t1.owner2 AND t2.person2=t3.person3 ignoring t1.type1-t3.type3 relation
| owner1 | type1 | owner2 | person2 | type3 | person3 |
|--------|-------|--------|---------|-------|---------|
| O1 | T1 | O1 | P1 | T1 | P1 |
| O1 | T2 | O1 | P1 | T1 | P1 |different types
| O2 | T1 | O2 | P1 | T1 | P1 |
| O1 | T1 | O1 | P2 | T1 | P2 |
| O1 | T2 | O1 | P2 | T1 | P2 |different types
| O1 | T1 | O1 | P2 | T2 | P2 |different types
| O1 | T2 | O1 | P2 | T2 | P2 |
where t3.person2=t2.person3 AND t3.type3=t1.type1 ignoring t1.owner1-t2.owner2 relation
| owner1 | type1 | owner2 | person2 | type3 | person3 |
|--------|-------|--------|---------|-------|---------|
| O1 | T1 | O1 | P1 | T1 | P1 |
| O2 | T1 | O1 | P1 | T1 | P1 |different owners
| O1 | T1 | O1 | P2 | T1 | P2 |
| O1 | T2 | O1 | P2 | T2 | P2 |
| O2 | T1 | O1 | P2 | T1 | P2 |different owners
| O1 | T1 | O2 | P1 | T1 | P1 |different owners
| O2 | T1 | O2 | P1 | T1 | P1 |
where r1.owner1 = r2.owner2 and r2.person2 = r3.person3 and r1.type1 = r3.type3 (4 results)
| owner1 | type1 | owner2 | person2 | type3 | person3 |
|--------|-------|--------|---------|-------|---------|
| O1 | T1 | O1 | P1 | T1 | P1 |
| O1 | T1 | O1 | P2 | T1 | P2 |
| O1 | T2 | O1 | P2 | T2 | P2 |
| O2 | T1 | O2 | P1 | T1 | P1 |
Every row is unique and every type,person and owner are same row-wise.
Unwanted output occured when a relation ignored (for example person.when person2-person3 relation ignored) person2s via table2.owner2 and person3s via table3.type3 will all be added to result as if they are irrelevant fields.