JOIN SQL MULTIPLES TABLES

Question

JOIN SQL MULTIPLES TABLES

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

Good morning,

I would like to ask a question, this sql query is working, but repeat the records, any suggestions or corrections

select 
f.periodo 'PERIODO_INFORMADO', s.rut_emp 'RUT_EMPRESA', e.nom_empresa 'DESCRIPCION_RUT',r.id_reg 'REGION',
r.nom_reg'DESCRIPCION_REGION', s.cod_sist 'SISTEMA', s.nom_sist 'DESCRIPCION_SISTEMA',f.cod_obra 'CODIGO_OBRA',
f.nom_obra'NOMBRE_OBRA', f.anyo_obra'AÑO_CONSTRUCCION', eu.id_est'ESTADO_USO', eu.nom_est'DESCRIPCION_ESTADO', sr.id_secrio'SECCION_RIO',
sr.nom_secrio'DESCRIPCION_SECCION',f.num_fuente'FUENTE', f.nom_fuente'DESCRIPCION_FUENTE', f.cota'COTA', d.id_dsr'DESARENADOR', d.nom_dsr'DESCRIPCION_DESARENADOR',
f.caudal'CAUDAL_DISEÑO', t.id_tc'TIPO_CAPTACION', t.nom_tc'DESCRIPCION_CAPTACION',f.otro_tipo'OTRO_TIPO_CAPTACION', f.barrera_long'LONGITUD_BARRERA', 
f.barrera_alt'ALTURA_BARRERA', f.cap_op'CAPACIDAD_OPERATIVA', tlm.id_tlm'TELEMETRIA', tlm.nom_tlm'DESCRIPCION_TELEMETRIA', 
tlc.id_tlc'TELECONTROL', tlc.nom_tlc'DESCRIPCION TELECONTROL', f.utm_n'UTM_NORTE', f.utm_e'UTM_ESTE', f.vigencia'VIGENCIA'
from fam101 f
join 
localidad l on f.cod_local=l.cod_local
join 
region r on r.id_reg = l.id_reg
join 
sis_tarifario s on r.id_reg = s.id_reg 
join
empresa e on s.rut_emp = e.rut_emp
join 
est_uso eu on f.id_est = eu.id_est
join
sec_rio sr on f.id_secrio = sr.id_secrio
join 
desarenador d on f.id_dsr = d.id_dsr
join
tipo_cap_rio t on f.id_tc = t.id_tc
join 
tlmetria tlm on f.id_tlm = tlm.id_tlm
join 
tlcontrol tlc on f.id_tlc = tlc.id_tlc

sql sql-server

asked by koxe_24 17.01.2018 в 14:42

source

2 answers

Problem generating APK and showing Sha1 using console SSIS: Problems with data types DT_TEXT in Script Task

score 1 · Answer 1

@PatricioMoracho He's absolutely right. But, at your request for any suggestion, because if you want to avoid repeating certain records from their insertion, I would recommend that in the definition of the table you create unique indexes:

ALTER TABLE 'TablaEjemplo' 
  ADD UNIQUE INDEX 'indice_prueba' ('campo1', 'campo2', 'campo3');

This prevents the insertion of records in a table that repeat a pattern described by the unique index. Make sure you do not leave fields without taking into account or take fields to spare and pass strict.

This can save you a lot of time instead of doing group by or distinct (in case you have tables with more than hundreds of thousands of records). Even for peace of mind to ensure you have some control of the fields in the records entered in your tables. In addition to avoiding duplicates in tables that will have many records such as policy issues, visit records, etc., you will save a lot of storage and (as a result of the use of group by or distinct) execution time.

If you want to delete that index for some reason, you can run the following query:

ALTER TABLE 'TablaEjemplo' 
  DROP INDEX indice_prueba;

And re-create it, etc. If you want to see the fields that use that index you can use:

SELECT *
FROM information_schema.statistics
WHERE TABLE_NAME = 'TablaEjemplo'
  AND INDEX_NAME = 'indice_prueba';

score 0 · Answer 2

You see more than one query because there is a one-to-many relationship in one of your join with the main query table fam101 .

First check if the join are right.

Assuming there are two records in the sec_rio table when doing, for example, a group by f.codigo would leave only one row for each record of fam101 , but of the two records of sec_rio would only show one. Usually the one that was first inserted into the database and information from the second record would be omitted.

If you need the information of both registers, you can use the function group_concat(sr.id_secrio) in the selection of columns before the from for the two values appear separated by commas in the same register.