I am consulting several tables, the main table t_persona
has 441,153 records and the table t_evaluación
has 471,558. The evaluation table can have several times the code of the person since a person can have 1 or more evaluations.
What I need is that making join
with table t_evaluación
brings me the same number of records that exist in t_personas
(441,153); but in doing so it brings me more records (471,558) than the total of the evaluation table. And so with the other tables.
The structure of table t_persona
is:
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,
CONSTRAINT codigo_persona PRIMARY KEY (codigo_persona),
CONSTRAINT edadmaxima CHECK (edad <= 120),
CONSTRAINT edadminima CHECK (edad >= 0),
CONSTRAINT validargenero CHECK (genero >= 1 AND genero <= 2)
table evaluacion
:
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),
codigo_condicion_especial 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_evaluacion_codigo_condicion_especial_fkey FOREIGN KEY (codigo_condicion_especial)
REFERENCES vulnerabilidad.t_condicion_especial (codigo_condicion_especial) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT t_persona_t_evaluacion_fk FOREIGN KEY (codigo_persona)
REFERENCES vulnerabilidad.t_persona (codigo_persona) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT "validarCbi" CHECK (cbi > 49 OR cbi < 387),
CONSTRAINT "validarDescripcionSituacion" CHECK (codigo_descripcion_situacion > 0 OR codigo_descripcion_situacion < 4),
CONSTRAINT validarcbi CHECK (cbi >= 50 AND cbi <= 386),
CONSTRAINT validarpeso CHECK (peso >= 1.00::double precision AND peso <= 150.00::double precision)
table t_ubicacion_persona
codigo_ubicacion_persona character varying(10) NOT NULL,
codigo_estado character varying(2) NOT NULL,
codigo_municipio character varying(2) NOT NULL,
codigo_parroquia character varying(2) NOT NULL,
codigo_sector character varying(10) NOT NULL,
codigo_persona character varying(12),
id serial NOT NULL,
numero_ubicacion integer,
cedula_ubicacion character varying,
poblacion_indigena character varying(200),
CONSTRAINT t_ubicacion_persona_pk PRIMARY KEY (codigo_ubicacion_persona),
CONSTRAINT "foranes t_persona" FOREIGN KEY (codigo_persona)
REFERENCES vulnerabilidad.t_persona (codigo_persona) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
table t_registro_persona
(
id serial NOT NULL,
codigo_registro character varying(12) NOT NULL,
codigo_persona character varying(12) NOT NULL,
codigo_evaluacion character varying(12) NOT NULL,
CONSTRAINT t_registro_persona_pkey PRIMARY KEY (codigo_registro, codigo_persona, codigo_evaluacion)
)
and table t_registro
:
(
id serial NOT NULL,
codigo_registro character varying(12) NOT NULL,
fecha_registro timestamp without time zone,
codigo_usuario character varying(12),
codigo_transcriptor character varying(12),
CONSTRAINT codigo_registro PRIMARY KEY (codigo_registro)
)
I need to do join
to all these tables; because it is necessary for the report to have data of the evaluation, of the location, the data of the person and the date of the registration, which is stored in the table t_registro
. But to know the registration date of each person is done through the table t_registro_persona
.
I use the following query for the query
select * from vulnerabilidad.t_persona as p
join vulnerabilidad.t_evaluacion as ev on p.codigo_persona = ev.codigo_persona
join vulnerabilidad.t_ubicacion_persona as up on up.codigo_persona = p.codigo_persona
join vulnerabilidad.t_registro_persona as rp on rp.codigo_persona = p.codigo_persona
join vulnerabilidad.t_registro as r on r.codigo_registro = rp.codigo_registro
This brings me as a result 547,201 records. What I need is all the information in the tables but only with the number of records in the table people (441,153).