Join Problem

[former_member557244]

Sir I have these codes

SELECT distinct T1."DocDate",
T1."DocNum",
T1."CardCode",
T1."CardName",
--T2."Quantity",
T0."Debit",
T0."Credit"
	
from
	JDT1 T0
	inner join OPCH T1 ON T0."TransId" = T1."TransId" AND T0."TransType" = 18
  inner join PCH1 T2 ON T2."DocEntry" = T1."DocEntry"
  where T0."RefDate"='2021-09-16'
  and T1."DocNum" =353
  and (T0."Debit"<>0 or T0."Credit"<>0)
  and left(T1."CardCode",1)='V'
  and T1."CANCELED"='N'
  order by T1."DocDate",T1."DocNum"

The code work fine but When add this column

T2."Quantity",

The every row appear as double like shown here

Please help me how to get single records.

[chathia]

Hi Tariq,

This is more related to understanding the cardinality between the tables you are joining. If you are not sure on this aspect, I would suggest you to break the JOIN into individual selects and verify the results first.

I have given the breakup of your join query (but unable to check the syntax) which you can further correct it as you need.

--T0
SELECT 
T0."TransId",
T0."Debit",
T0."Credit"	
from JDT1 T0
  where T0."RefDate"='2021-09-16' and T0."TransType" = 18
  and (T0."Debit"<>0 or T0."Credit"<>0);

--T1
SELECT 
T1."DocDate",
T1."DocNum",
T1."CardCode",
T1."CardName",
T1."TransId" ,
T1."DocEntry"
from OPCH T1 
  where 
      T1."DocNum" =353
  and left(T1."CardCode",1)='V'
  and T1."CANCELED"='N';

Based on the above T1 result, find the "DocEntry" and manually apply it as a filter in the below T2 Select, to check on the multiple entries issue.

--T2
SELECT 
T2."DocEntry",
T2."Quantity",
T2.*	
from
  PCH1 T2 ;

Once you are able to understand this part, you could apply additional filter or aggregation functions on the quantities like SUM, MIN, MAX, AVG etc.

Regards,

Chathia.