Duplicate sqlserver join

Question

Duplicate sqlserver join

Navigation

#1 by (0 votes)

0

Hello good day I have a question, I have a table that says products and one that says equivalents and linked results that I do the query with joins where is this way:

Productos  Equivalentes   Ligados
00697000   MS-92692       MU-41309-P          
           MU-41309-P

but in my query it brings me the result like this:

Producto            Equivalente         Ligado
00697000            MS-92692            MU-41309-P        
00697000            MU-41309-P          MU-41309-P

and then in the linked I repeat the product MU-41309-P and I do not want to repeat it since it is only once in the table I occupy something like this:

Producto            Equivalente         Ligado
00697000            MS-92692            MU-41309-P        
00697000            MU-41309-P          NULL

I have the code like this:

    SELECT PROD.C1 AS CVE_PROD, EQUIVALENTES.C1 AS EQUIVALENTES, LIGADOS.C1 AS 
    LIGADOS
    FROM PRODUCTO AS PROD
    LEFT JOIN EQUIVALENTE AS EQUIVALENTES ON EQUIVALENTES.C2=PROD.C1
    LEFT JOIN LIGADO AS LIGADOS ON LIGADOS.C2=PROD.C1
   WHERE PROD.C1='00697000'
   ORDER BY PROD.C1

mysql sql-server oracle-11g

asked by Javier Solis 05.10.2018 в 00:49

source

1 answer

Does not deploy the html with the Glassfish 5.0 server and netbeans 8.2 Update to external database from android

score 0 · Accepted Answer

the reason why it shows you the linked 'MU-41309-P' in both registers is that the product is '00697000' for both registers. if I show you a null as you ask, it would be a lie, since the product 00697000 does have a link. I explain this to you only to understand the "point of view" of the database.

Having said that, what you ask would be something like this:

SELECT PROD.C1 AS CVE_PROD, EQUIVALENTES.C1 AS EQUIVALENTES, 
CASE WHEN Row_Number() Over(Partition By LIGADOS.C1 Order By PROD.C1) = 1 THEN LIGADOS.C1 ELSE '' END AS 
LIGADOS
FROM PRODUCTO AS PROD
LEFT JOIN EQUIVALENTE AS EQUIVALENTES ON EQUIVALENTES.C2=PROD.C1
LEFT JOIN LIGADO AS LIGADOS ON LIGADOS.C2=PROD.C1
WHERE PROD.C1='00697000'
ORDER BY PROD.C1

Try to see, since I can not prove it, I do not know if I was wrong about something