join brings me more data

Navigation
0

I am making a query to 2 tables the main table t_person has 128176 records and the table t_evaluation has 135839 the evaluation table can have several times the code of the person since a person can have 1 or more evaluations What I need to do is to make the join with the table t_evaluacion bring me the last record of each person who is in t_personas (128176) but doing so brings me 135873, brings me more records than the total of the evaluation table.

Table People: 

codigo_persona character varying(12) NOT NULL,
  id character varying NOT NULL DEFAULT nextval('vulnerabilidad.t_persona_id_seq'::regclass),
  nombres_apellidos character varying(100) NOT NULL,
  cedula character varying(10) NOT NULL,
  fecha_nacimiento date NOT NULL,
  edad integer NOT NULL,
  genero integer NOT NULL,
  meses integer,
  dias integer,

Of this table I only need the columns of age, months, days, gender

Evaluation table 

codigo_evaluacion character varying(10) NOT NULL,
  id serial NOT NULL,
  codigo_persona character varying(12) NOT NULL,
  codigo_descripcion_situacion integer NOT NULL,
  cbi integer NOT NULL,
  peso double precision NOT NULL,
  talla double precision NOT NULL DEFAULT 0.0,
  embarazo character varying NOT NULL DEFAULT false,
  fecha_abordaje date NOT NULL,
  codigo_origen character varying(12),
  CONSTRAINT codigo_evaluacion PRIMARY KEY (codigo_evaluacion),
  CONSTRAINT t_estado_nutricional_t_evaluacion_fk FOREIGN KEY (codigo_descripcion_situacion)
      REFERENCES vulnerabilidad.t_estado_nutricional (codigo_descripcion_situacion) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT t_persona_t_evaluacion_fk FOREIGN KEY (codigo_persona)
      REFERENCES vulnerabilidad.t_persona (codigo_persona) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,

From this table I need the columns cbi, weight, size, code_description_situation, pregnancy, source_code

When doing the join of the table people to the evaluation table and calling the pregnancy field brings me all the resgistros of the evaluation table, but I only need the last aggregate record

this is the code 

select * from vulnerabilidad.t_evaluacion as e right join  
    vulnerabilidad.t_persona as p on p.codigo_persona = e.codigo_persona

I put a distinct to the code_person in the evaluation table and it brings me 128176 records, to bring another column of the evaluation table brings me more records

here the code 

select distinct(e.codigo_persona), e.embarazo from 
    vulnerabilidad.t_evaluacion as e left join vulnerabilidad.t_persona as p 
    on p.codigo_persona = e.codigo_persona
    
asked by Angel Gutierrez 10.07.2018 в 17:48
source

2 answers

1

Assuming the pk for t_evaluacion is the column id , the query to get the last row for each codigo_persona could be something like this: 

select  t1.*
    from vulnerabilidad.t_evaluacion as t1
    inner join (select  codigo_persona,
                max(id) as id
            from vulnerabilidad.t_evaluacion 
            group by codigo_persona
        ) as t2
        on t2.codigo_persona = t1.codigo_persona
        and t2.id = t1.id

Important : this works whenever the id is effectively incremental, that is, each new row corresponds to a id greater than the previous one, then we can say that the id=2 is newer that id=1 .

Now your query could take advantage of the previous query to do something like this: 

select  e.codigo_persona, 
        e.embarazo 
    from  vulnerabilidad.t_persona p
    left join (select   t1.*
            from vulnerabilidad.t_evaluacion  as t1
            inner join (select  codigo_persona,
                        max(id) as id
                    from vulnerabilidad.t_evaluacion 
                    group by codigo_persona
                ) as t2
                on t2.codigo_persona = t1.codigo_persona
                and t2.id = t1.id
        ) e
        on p.codigo_persona = e.codigo_persona
    
answered by 10.07.2018 / 19:52
source
0

My advice is that you do not use the distinct because you are pulling different data that you can get with the select simple, try it like this: 

select e.codigo_persona, e.embarazo from 
    vulnerabilidad.t_evaluacion as e left join vulnerabilidad.t_persona as p 
    on p.codigo_persona = e.codigo_persona;
    
answered by 10.07.2018 в 17:54
How do I create a backup in PostgreSQL with .batch Problem in urls.py