Data filtering between two SQL Server tables

Question

Data filtering between two SQL Server tables

Navigation

#1 by (1 votes)

1

Good afternoon, I have a problem wanting to select specific data when making a query to two tables; The tables are the following:

create table persona
(
id int identity (1,1) not null,
nombre varchar (350) not null,
apellido varchar (350) not null,
ci varchar (125) unique not null, --tomando en cuenta el departamento
usuario varchar (250) unique not null,
contrasenia varchar (350) not null,
fechaInicio date not null,
estado varchar (8) not null,
constraint pk_persona primary key (id)
);
create table rol
(
id int identity (1,1) not null,
nombre varchar (350) not null,
persona int not null,
fecha date not null,
constraint pk_rol primary key (id),
constraint fk_rol_persona foreign key (persona)
references persona (id)
);



insert into persona values ('juan', 'perez', '1515', 'jp', '1234', '12/12/2012', 'activo')
insert into persona values ('pedro', 'lopez', '1616', 'pl', '1598', '13/12/2012', 'activo')
insert into rol values ('encargado', 1,'10/05/2018')
insert into rol values ('director', 1,'12/05/2018')
insert into rol values ('encargado', 2,'20/05/2018')
insert into rol values ('encargado', 2,'22/05/2018')

The query I was using is this:

select p.id, p.nombre, p.apellido, p.ci, p.usuario, p.contrasenia, p.fechaInicio, p.estado, r.nombre , r.fecha as 'fecha rol'
from persona p, rol r where p.id=r.persona

The result is this:

But what I need is for you to return the data that you have based on the last date to your granted role:

sql-server select consulta

asked by Alvaro Mauricio Angulo Riquelm 22.05.2018 в 19:41

source

1 answer

How to make reference in package.json to another module of my project? Convert the Difference of two dates into an integer

score 1 · Accepted Answer

I understand that what you are looking for is this:

select  p.id, 
        p.nombre, 
        p.apellido, 
        p.ci, 
        p.usuario, 
        p.contrasenia, 
        p.fechaInicio, 
        p.estado, 
        r.nombre , 
        r.fecha as 'fecha rol'
    from persona p
    -- Ultimo rol por persona
    left join (select persona,
            MAX(fecha) as 'fecha'
            from rol r 
            group by persona
        ) m
        on m.persona=p.id
    left join rol r
        on r.persona = p.id
        and r.fecha = m.fecha

The main thing about this query is to obtain the last role date by persona , which would be MAX(fecha) , with this information we create a subquery that allows us to filter only the roles whose date is mentioned.

Some comments:

It is recommended to use explicit JOIN using the ON clause
We use LEFT simply to show all people, have a role or not, if you only want those that have a role, modify the join for a INNER .
This query assumes that there can not be repeated dates for the same person. That is, having more than one role in the same period of time.