Query to relate the sales orders and the client invoices

[former_member325743]

Hi everyone, I need to make a query that show me some info about an invoice and its related sales order. I already did the query and it shows the info in a "correct" way.

The problem is that I got repeated lines with the same info, what I need is someone who can help me to take off the extra lines. This is my query:

SELECT Distinct t2.DocNum 'Factura',  t0.DocNum 'Orden',T2.CardName 'Cliente',
t3.u_p_unitario 'Ordenado',T1.U_p_unitario 'Realizado',t1.Dscription, (t1.PriceBefDi*t1.rate) 'Costo'
, t3.linetotal 'Subtotal Ordenado', t3.vatsum 'Impuestos',t3.GTotal ' $ Total Ordenado'
, t1.linetotal 'Subtotal Realizado', t1.vatsum 'Impuestos',t1.GTotal ' $ Total Realizado'
FROM ORDR T0  
inner JOIN INV1 T1 ON T1.BaseEntry=T0.DocEntry AND T1.BaseType='17'  
inner JOIN OINV T2 ON T2.DocEntry=T1.DocEntry  
INNER JOIN RDR1 T3 on t0.DocEntry=t3.DocEntry
Where T2.CardName='Toy Automotores S.A de C.V' order by t1.Dscription

And the result is this one:

As you can see. I got the same item like 2 or three times, with respect to the invoice and order numbers it doesn't matter that it is repeated.

I tried with "group by" but didn´t work or maybe I didn´t how to apply it.

In short, I only want to do not have the item / Description repeated.

[Johan_H · Accepted solution]

Hi Anderson,

The problem is that in your query you have not linked the invoice and order lines. Please try this, and note that I only added the line link to the RDR1 join:

SELECT Distinct t2.DocNum 'Factura'
              , t0.DocNum 'Orden'
              , T2.CardName 'Cliente'
              , t3.u_p_unitario 'Ordenado'
              , T1.U_p_unitario 'Realizado'
              , t1.Dscription
              , (t1.PriceBefDi*t1.rate) 'Costo'
              , t3.linetotal 'Subtotal Ordenado'
              , t3.vatsum 'Impuestos'
              , t3.GTotal ' $ Total Ordenado'
              , t1.linetotal 'Subtotal Realizado'
              , t1.vatsum 'Impuestos'
              , t1.GTotal ' $ Total Realizado'
FROM ORDR T0  
     INNER JOIN INV1 T1 ON T1.BaseEntry = T0.DocEntry AND T1.BaseType = '17'  
     INNER JOIN OINV T2 ON T2.DocEntry = T1.DocEntry  
     INNER JOIN RDR1 T3 on t0.DocEntry = t3.DocEntry AND T1.BaseLine = T3.LineNum
WHERE T2.CardName = 'Toy Automotores S.A de C.V'
ORDER BY t1.Dscription

Regards,

Johan

[former_member325743 · Accepted solution]

Thanks a lot, Johan!