Se estan repitiendo datos con inner join

Question

Estoy realizando una consulta de varias tablas en sql y las uno por inner join y el problema es que me esta repitiendo el mismo dato varias veces esta es la consulta

SELECT A.DESCATALOGADO
      ,A.DPTO
      ,A.CODARTICULO
      ,A.REFPROVEEDOR
	  ,A.DESCRIPCION
	  ,L.CODBARRAS
	  ,S.TALLA
	  ,S.COLOR
	  ,L.ULTIMOCOSTE
	  ,L.COSTEMEDIO
	  ,S.CODALMACEN
	  ,S.STOCK
	  ---,S.FABRICACION
	  ---,S.PEDIDO
	  ---,S.ASERVIR
	  ---,S.ENTRANSITO
	  ,L.FECHAULTCOMPRA
	  ,L.PRECIOULTCOMPRA
	  ,L.FECHAULTVENTA
	  ,P.IDTARIFAV
	  ,P.PNETO AS 'COSTO_B2B'
	  	  
	  
FROM STOCKS S  WITH(NOLOCK) 
	INNER JOIN ARTICULOS A WITH(NOLOCK) ON A.CODARTICULO=S.CODARTICULO
	INNER JOIN ARTICULOSLIN L WITH(NOLOCK) ON S.CODARTICULO = L.CODARTICULO AND 
                      S.TALLA = L.TALLA AND S.COLOR = L.COLOR
    INNER JOIN PRECIOSVENTA P WITH(NOLOCK) ON P.CODARTICULO = A.CODARTICULO

WHERE A.DPTO = 1
     and (A.DESCATALOGADO = 'F')
	 AND (S.STOCK<>0 OR S.FABRICACION<>0 OR S.PEDIDO<>0 OR S.ASERVIR<>0 OR S.ENTRANSITO<>0 )
      and S.CODALMACEN NOT LIKE ('2%')
	  AND (P.IDTARIFAV='2')

y el resultado repitiendo valores, deveria mostrar datos sin repetir

Answer 1

El lenguaje de ese forum es inglese. (The language of this forum is English.)

I am not sure that I understand the question. That may be due to that I don't know Spanish too well, but I am not sure. It seems that you are asking why you get repeated values. But since you don't show any examples, it's difficult to understand what is repeating.

What are the keys for these tables? For the query to make sense the key column of ARTICULOS and PRECIOSVENTA must be CODARTICULO, and the key for ARTICOLUSLIN must be (CODARTICULO, TALLA, COLOR). If you join in incomplete keys, this often leads to row multiplication and repeated values.

Also, take out all those NOLOCK. Adding NOLOCK to a query without understanding what you are doing will lead to that you every once in a while get incorrect results.

Answer 2

Hi @JOHAN USEDA

The use of INNER JOIN does not mean that the final result is necessarily non-repeated. If you have duplicate data in your tables, of course the final result set will also have duplicate data.

How about adding DISTINCT in your SELECT clause? SELECT DISTINCT A.DESCATALOGADO,A.DPTO,A.CODARTICULO,...

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.