SQL ORACLE - Join between tables not normalized-CodePudding

I have access to my corporate Oracle database, I am granted SELECT TABLE right only.

I have 2 tables containing the following data. The only fields that link the 2 tables are DEPT_ID AND SECTOR_ID. as you can see below with the result expected:

If I type the following select statement, I get duplicates of course.

select t1.dir_id,
       t1.dept_id,
       t1.sector_id,
       t2.place_id,
       t2.amount
from test1 t1 , 
test2 t2
where t1.dept_id = t2.dept_id
and t1.sector_id = t2.sector_id;

Thanks in advance if you can help (below, tables and content for the example)

CREATE TABLE Test1 (
  DIR_ID INT,
  DEPT_ID INT,
  SECTOR_ID INT,
  OTHER INT);
  
CREATE TABLE Test2 (
  DEPT_ID INT,
  SECTOR_ID INT,
  PLACE_ID INT,
  AMOUNT INT);
  
INSERT INTO Test1 VALUES (1,1,1,1);
INSERT INTO Test1 VALUES (1,1,1,2);
INSERT INTO Test1 VALUES (1,1,1,3);
INSERT INTO Test1 VALUES (1,2,1,1);

INSERT INTO Test2 VALUES (1,1,1,10);
INSERT INTO Test2 VALUES (1,1,1,10);
INSERT INTO Test2 VALUES (1,1,1,20);
INSERT INTO Test2 VALUES (1,2,1,10);

Fiddle http://sqlfiddle.com/#!9/c5a658/1/0

CodePudding user response：

You may join the test2 table with the distinct dir_id,dept_id,sector_id from test1 as the following:

select t1.dir_id,
       t1.dept_id,
       t1.sector_id,
       t2.place_id,
       t2.amount
from (select distinct dir_id,dept_id,sector_id from test1) t1 
join 
test2 t2
on t1.dept_id = t2.dept_id
and t1.sector_id = t2.sector_id;

See a demo.

CodePudding user response：

In general, the join condition in every join should fully include the primary key columns of at least one of the tables. Your example does not do that, which is why there are problems.

To construct a join relation between two tables, A and B, take every row in A and match each one with every row in B. This intermediate result is called a Cartesian product. If table A has 50 rows and table B has 1,000 rows, this Cartesian product will have 50 x 1,000 or 50,000 rows.

The result of the join will be every match in the Cartesian product of A and B that meets the join condition.

In your test data, table TEST1 has 3 rows that have (DEPT_ID,SECTOR_ID) = (1,1). Table TEST2 likewise has 3 such rows. So, your result will include 9 (3x3) rows that have (DEPT_ID,SECTOR_ID) = (1,1).

That is all anyone can say about this without more information from you. You seem to only one 3 rows in the result for (1,1) but you don't say how you want to do this.

You could, for example, use the ROW_NUMBER() window function to arbitrarily number the rows in TEST1 and TEST2 and join the 1st numbered row in TEST1 to the 1st numbered row in TEST2. But you need to give the requirements.