Consultation with join brings me more records than expected

Question

Consultation with join brings me more records than expected

Navigation

#1 by (2 votes)

0

I need to make a join between 2 tables, I have the person table which contains 58422 records, and I have the evaluation table which contains 59175 records what I need is that it only brings me to join the total of the person table and not that of the evaluation table

select
    p.codigo_persona,
    ev.codigo_evaluacion
    from vulnerabilidad.t_persona p
    join vulnerabilidad.t_evaluacion as ev 
         ON p.codigo_persona = ev.codigo_persona'

a person can have several evaluations but I only need the records of the people not the amount of evaluations, of course with the data of the evaluation table.

sql postgresql

asked by Angel Gutierrez 08.05.2018 в 16:15

source

1 answer

how can I filter the data I show via the ajax request? Add template for email, already defined in .cshtml

score 2 · Accepted Answer

If you need any indifferent evaluation if it is the first or last one, one option would be to use ROW_NUMBER :

WITH CTE AS
(
    SELECT  *,
            ROW_NUMBER() OVER(PARTITION BY codigo_persona ORDER BY codigo_persona) AS RN
    FROM vulnerabilidad.t_evaluacion
)
SELECT  p.codigo_persona,
        ev.codigo_evaluacion
FROM vulnerabilidad.t_persona p
INNER JOIN CTE as ev 
    ON p.codigo_persona = ev.codigo_persona
WHERE ev.RN = 1
;