How to make Join of 2 tables without repeating data?

Question

I have 2 tables, a call SR7010 and another call SR3010. The primary key of the two is the field R7_MAT and R3_MAT.

The query I’m trying to execute is this:

SELECT R7_FILIAL, R7_MAT, R7_DESCFUN, R7_DATA, R3_VALOR FROM SR7010 AS sr7
INNER JOIN SR3010 AS sr3 ON (sr3.R3_MAT = sr7.R7_MAT)
WHERE sr7.R7_MAT = 997

What happens is this: the field R3_VALOR appears with the repeated data, but the other fields (starting with R7) appear normally, and when the field R3_VALOR changes, the data of the fields R7 are repeated, thus successively.

How to make it correctly bring the information, that is, only 3 lines, as below?

Are records of salary changes/position.

Ex: In the 1st line, with the position of Computer Assist, on the date of 20151029, is with the salary 50,00.

On the date of 20151115, still as computer Assist, salary was changed to 55,00.

On the date of 20151126, the position changed to Anal. de informatica, with the salary of 60,00.

What happens is that the data is being listed 3 times (amount of records of actually existing changes), but the value field, because it is another table, maybe, is displaying the same value 3 times to then change to another value and repeat again 3 times (number of changes records), and so on.

When I do a table search SR7010 across the countryside R7_MAT = 997, the database returns me 3 records. The same occurs when I do a search in the table SR3010 across the countryside R3_MAT = 997. It also returns 3 records, so I’m finding it strange.

Answer 1

I changed INNER JOIN to include more relationships and it worked correctly.

SELECT sr7.R7_FILIAL, sr7.R7_MAT, sr7.R7_DATA, sr7.R7_DESCFUN, sr3.R3_VALOR, sr3.R3_FILIAL FROM SR7010 AS sr7
INNER JOIN SR3010 AS sr3 ON (sr3.R3_FILIAL = sr7.R7_FILIAL AND sr3.R3_MAT = sr7.R7_MAT AND sr3.R3_DATA = sr7.R7_DATA)
WHERE sr7.R7_MAT = 997

Answer 2

The only solution was to create a cursor as follows.

declare @SR7010 table
(
  R7_FILIAL varchar(20),
  R7_MAT varchar(20), 
  R7_DESCFUN varchar (100), 
  R7_DATA date,
  R3_VALOR numeric(18,2)
)

declare @SR7010_new table
(
  R7_FILIAL varchar(20),
  R7_MAT varchar(20), 
  R7_DESCFUN varchar (100), 
  R7_DATA date,
  R3_VALOR numeric(18,2)
)

insert into @SR7010 values
('0301','000997', 'ASSIST. INFORMATICA', '20151029' , 50.00),
('0301','000997', 'ASSIST. INFORMATICA', '20151115' , 50.00),
('0301','000997', 'ANAL. DE INFORM',     '20151126' , 50.00),
('0301','000997', 'ASSIST. INFORMATICA', '20151029' , 55.00),
('0301','000997', 'ASSIST. INFORMATICA', '20151115' , 55.00),
('0301','000997', 'ANAL. DE INFORM',     '20151126' , 55.00),
('0301','000997', 'ASSIST. INFORMATICA', '20151029' , 60.00),
('0301','000997', 'ASSIST. INFORMATICA', '20151115' , 60.00),
('0301','000997', 'ANAL. DE INFORM',     '20151126' , 60.00)


declare @R7_FILIAL varchar(20), @R7_MAT varchar(20), @R7_DESCFUN varchar (100), 
        @R7_DATA date, @R3_VALOR numeric(18,2), @linha int = 0,
        @Linhafinal int =(select top 1 count(R7_FILIAL)
                            from @SR7010
                            group by R7_FILIAL, R7_MAT, R7_DESCFUN, R7_DATA)


DECLARE db_cursor CURSOR FOR  
select R7_FILIAL, R7_MAT, R7_DESCFUN, R7_DATA
from @SR7010 E
group by R7_FILIAL, R7_MAT, R7_DESCFUN, R7_DATA
ORDER BY R7_DATA 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @R7_FILIAL, @R7_MAT, @R7_DESCFUN, @R7_DATA  

WHILE @@FETCH_STATUS = 0   
BEGIN   

       if(@linha = 0)
       begin
            insert into @SR7010_new 
            Select @R7_FILIAL, @R7_MAT, @R7_DESCFUN, @R7_DATA,( select min(R3_VALOR) from @SR7010 where R7_DATA = @R7_DATA)
            set @linha = 1;
       end
       else
       begin
            set @linha = @linha + 1; 
            if(@Linhafinal = @linha)
            begin
                insert into @SR7010_new 
                Select @R7_FILIAL, @R7_MAT, @R7_DESCFUN, @R7_DATA,(select top 1 t.R3_VALOR from @SR7010 t where t.R7_DATA = @R7_DATA order by t.R7_DATA desc, R3_VALOR desc)
            end
            else
            begin
                insert into @SR7010_new 
                Select @R7_FILIAL, @R7_MAT, @R7_DESCFUN, @R7_DATA,(select top 1 t.R3_VALOR from @SR7010 t where t.R7_DATA > @R7_DATA order by t.R7_DATA desc)
            end
       end
       FETCH NEXT FROM db_cursor INTO @R7_FILIAL, @R7_MAT, @R7_DESCFUN, @R7_DATA    
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

select * from @SR7010_new