MySQL query repeated data

Question

MySQL query repeated data

Navigation

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

2

I'm doing a query on phpMyadmin, everything worked fine, but I was taking data from other tables, however, when I added the LEFT JOIN, everything was still working perfectly. The problem is when in the SELECT I put the name of the column to be displayed ( TAVA_MCONV , or any other), that is when the duplication of the results is generated.

I leave here below the query that does not generate duplicity

SELECT DISTINCT(TPRE_IDPRE), TPRO_NOMBR,TPRE_NPRED,TPRE_UBICA,TPRE_RPROP,TPRE_KMINI,
          TPRE_KMFIN,TPRE_LONGI,TPRE_MUPIO,TPRE_SUADI,TGES_GES01,
          TGES_GES02,TGES_GES03,TGES_GES04,TGES_GES05,TGES_GES06,
          TGES_GES07,TGES_GES08,TGES_GES09,TGES_GES10,TGES_GES11,
          TGES_GES12,TGES_GES13,TGES_GES14,TGES_GES15,TGES_GES16,
          TGES_GES17,TGES_GES18,TGES_GES19,TGES_GES20,TGES_GES21,
          TGES_GES22,TGES_GES23,TGES_GES24
          FROM tpredio AS tpre  
          INNER JOIN tpred_prop AS tpp ON tpre.TPRE_IDPRE=tpp.TPRP_IDPRE
          INNER JOIN tpropietario AS tpro ON tpp.TPRP_IDPRO=tpro.TPRO_IDPRO
          INNER JOIN tobra AS tob ON tob.TOBR_IDOBR=tpre.TPRE_IDOBR
          INNER JOIN tgestoria AS tges ON tges.TGES_IDPRE=tpre.TPRE_IDPRE
          LEFT JOIN trecursos AS trec ON trec.TREC_IDPRE=tpre.TPRE_IDPRE
          LEFT JOIN tpago AS tpa ON tpa.TPAG_IDREC=trec.TREC_IDREC
          LEFT JOIN tavaluo AS tava ON tpre.TPRE_IDPRE=tava.TAVA_IDPRE
          WHERE tpre.TPRE_IDOBR='6L4E1'
          ORDER BY tpre.TPRE_KMINI

this is the one that generates duplicity

SELECT DISTINCT(TPRE_IDPRE), TPRO_NOMBR,TPRE_NPRED,TPRE_UBICA,TPRE_RPROP,TPRE_KMINI,
          TPRE_KMFIN,TPRE_LONGI,TPRE_MUPIO,TPRE_SUADI,TGES_GES01,
          TGES_GES02,TGES_GES03,TGES_GES04,TGES_GES05,TGES_GES06,
          TGES_GES07,TGES_GES08,TGES_GES09,TGES_GES10,TGES_GES11,
          TGES_GES12,TGES_GES13,TGES_GES14,TGES_GES15,TGES_GES16,
          TGES_GES17,TGES_GES18,TGES_GES19,TGES_GES20,TGES_GES21,
          TGES_GES22,TGES_GES23,TGES_GES24,TAVA_MCONV
          FROM tpredio AS tpre  
          INNER JOIN tpred_prop AS tpp ON tpre.TPRE_IDPRE=tpp.TPRP_IDPRE
          INNER JOIN tpropietario AS tpro ON tpp.TPRP_IDPRO=tpro.TPRO_IDPRO
          INNER JOIN tobra AS tob ON tob.TOBR_IDOBR=tpre.TPRE_IDOBR
          INNER JOIN tgestoria AS tges ON tges.TGES_IDPRE=tpre.TPRE_IDPRE
          LEFT JOIN trecursos AS trec ON trec.TREC_IDPRE=tpre.TPRE_IDPRE
          LEFT JOIN tpago AS tpa ON tpa.TPAG_IDREC=trec.TREC_IDREC
          LEFT JOIN tavaluo AS tava ON tpre.TPRE_IDPRE=tava.TAVA_IDPRE
          WHERE tpre.TPRE_IDOBR='6L4E1'
          ORDER BY tpre.TPRE_KMINI

image corresponding to query 1

image corresponding to query 2

mysql phpmyadmin

asked by Erik Raúl González Páez 08.10.2018 в 22:14

source

2 answers

0

What you need is to do the inner join over a subquery, but you do not show the structure of your tables, so I can not know which way is appropriate.

SELECT tabla1 INNER JOIN (SELECT campo FROM Tabla2);

In parentheses I would recommend a distinct selection on results without duplicity), and be everything else.

answered by 08.10.2018 в 23:34

MySQL does not return data in a column How do I make the browser scroll to fit a modal?

score 2 · Accepted Answer

One way to avoid repeated rows would be to do a GROUP BY TPRE_IDPRE , removing the DISTINCT , for example:

SELECT 
          TPRE_IDPRE, TPRO_NOMBR,TPRE_NPRED,TPRE_UBICA,TPRE_RPROP,TPRE_KMINI,
          TPRE_KMFIN,TPRE_LONGI,TPRE_MUPIO,TPRE_SUADI,TGES_GES01,
          TGES_GES02,TGES_GES03,TGES_GES04,TGES_GES05,TGES_GES06,
          TGES_GES07,TGES_GES08,TGES_GES09,TGES_GES10,TGES_GES11,
          TGES_GES12,TGES_GES13,TGES_GES14,TGES_GES15,TGES_GES16,
          TGES_GES17,TGES_GES18,TGES_GES19,TGES_GES20,TGES_GES21,
          TGES_GES22,TGES_GES23,TGES_GES24,TAVA_MCONV
          FROM tpredio AS tpre  
          INNER JOIN tpred_prop AS tpp ON tpre.TPRE_IDPRE=tpp.TPRP_IDPRE
          INNER JOIN tpropietario AS tpro ON tpp.TPRP_IDPRO=tpro.TPRO_IDPRO
          INNER JOIN tobra AS tob ON tob.TOBR_IDOBR=tpre.TPRE_IDOBR
          INNER JOIN tgestoria AS tges ON tges.TGES_IDPRE=tpre.TPRE_IDPRE
          LEFT JOIN trecursos AS trec ON trec.TREC_IDPRE=tpre.TPRE_IDPRE
          LEFT JOIN tpago AS tpa ON tpa.TPAG_IDREC=trec.TREC_IDREC
          LEFT JOIN tavaluo AS tava ON tpre.TPRE_IDPRE=tava.TAVA_IDPRE
WHERE tpre.TPRE_IDOBR='6L4E1'
GROUP BY TPRE_IDPRE
ORDER BY tpre.TPRE_KMINI

Anyway, even if I show you the data that you expect, the query may not meet the SQL grouping standard. It is very likely that instead of a query problem itself you have a problem of normalization of the data.