Perform a left join and assign the value of a column conditioned by a field

Question

Perform a left join and assign the value of a column conditioned by a field

Navigation

#1 by (1 votes)

1

Good afternoon, I have 2 tables and I'm doing left join of T1 with T2 to extract the client code. The problem is that there are 2 client codes for the same (RUC) in some cases (this because of the client's condition, which in some cases have 2) and therefore the records are duplicated in T1 since in the T2 There are 2 codes for that client, I just need to take one.

I present the code so you can guide me:

SELECT 
    '1' AS CO_TURN,
    '1' AS CO_ESTA, 
    '1' AS CO_FPAG,T1.CO_PROD, 
    '1' AS CO_EMPR,T1.CO_TDOC,
    T1.NU_NSER,
    T1.NU_NDOC,
    NULL AS FE_TURN,
    LEFT(T1.FE_HORA,5) FE_HORA,
    '0' AS NU_NVAL,
    T1.FE_EMIS,
    NULL AS FE_EMIS_HORA,
    ROUND(T1.IM_TOTA/1.18,2) AS IM_VNET,
    ROUND(T1.IM_TOTA/1.18,10) AS IM_VVEN,
    ROUND (T1.IM_TOTA-ROUND(T1.IM_TOTA/1.18,4),2) IM_IGV,
    '0.0000000000' AS IM_DESC,
    T1.IM_TOTA,
    '0.0000' AS NU_GALN,
    '0.0000' AS NU_PREV,
    T1.IM_TOTA AS IM_PAGO,
    '0' AS NU_LADO,
    '1' AS CO_TMON,
    'N' AS ST_DVEN,
    0 AS CO_CHOF, 
    CASE T2.CO_CLIE WHEN NULL THEN NULL WHEN 0 THEN NULL ELSE T2.CO_CLIE END CO_CLIE,
    NULL AS NU_LIQI,
    NULL AS ST_CANJ,
    NULL AS ST_LIQI,
    NULL AS ST_PERI,
    NULL AS ST_ANNO,
    NULL AS CO_TRAB,
    '0' AS CO_VEHI,
    'SISGES' AS CO_USUA_CREA,GETDATE() AS FE_USUA_CREA,
    '0.1800' AS NU_IGV,
    '' AS CO_TDES_DOCU,
    NULL AS NO_CLIE_D1_DNI
FROM 
    MAR17_1 T1 LEFT JOIN TCCLIE T2 ON T1.CO_CLIE= T2.NU_RUC 
WHERE 
    T1.CO_TDOC IN (1,15,2)

sql join

asked by Klos Perú 04.04.2017 в 19:14

source

1 answer

Concatenate results of last numFile (id) registered and another update field why does not this program work?

score 1 · Answer 1

I do not understand your code very well, but I will put this hypothetical example where I want to get the total sales of the client, regardless of their RUC.

Assuming this table structure, this query sql returns the sales total of the NEo and SMA COMPANY clients.

SELECT
    SUM (Total) AS Total_Ventas,
    T2.Nombre AS Cliente
FROM
    Ventas AS T1
INNER JOIN (
    SELECT
        ID,Nombre
    FROM
        Cliente
    GROUP BY
        ID,Nombre
) AS T2 ON T1.ID_Cliente = T2.ID
GROUP BY
    T2.Nombre

The subquery T2 will return the Clients only once so your join will not repeat the data. The final result should be like the following.