How to join 2 tables without repeating data?

Navigation
2

I have 2 tables, one 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 perform 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: the R3_VALOR field appears with the repeated data, but the other fields (starting with R7 ) appear normally, and when the field R3_VALOR changes, the data in the R7 fields repeat, so on.

Howtomakeitbringtheinformationcorrectly,ieonly3lines,asbelow?

These are salary / charge change records.

Ex: In the first line, with the position of Assist Informatica, on the date of 20151029 , you have the salary 50,00 .

As of 20151115 , still as Computer Support, salary was changed to 55.00 .

As of 20151126 , the position changed to Anal. with the salary of 60,00 .

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

When I search the SR7010 table with the R7_MAT field = 997 , the database returns 3 records. The same occurs when I search the SR3010 table with the R3_MAT field = 997 . It also returns 3 records, so I find it strange.

    
asked by anonymous 19.08.2016 / 21:15

2 answers

2

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
    
22.08.2016 / 14:43
0

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
    
19.08.2016 / 21:33
How to select single data from a conditional join? Styling the label in an input file type