cancel
Showing results for 
Search instead for 
Did you mean: 

Join Problem

former_member557244
Participant
0 Kudos

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.

Accepted Solutions (0)

Answers (3)

Answers (3)

Jörg_Brandeis
Contributor

Hi Tariq,

the problem is the keyword DISTINCT. It removes duplicate rows in your query. The QUANTITY column makes rows different, that were identical without this column.

Regards,
Jörg

former_member557244
Participant
0 Kudos

Sir i removed Distinct but no difference. The records are still duplicated.

Jörg_Brandeis
Contributor

Of course there are duplicates, when you remove DISTINCT. But this keyword explains the different behaviour when adding an other column.

T2 brings multiple different quantities due to your JOIN condition. Is this, what you intend? E.g. in the first two rows in your example, these are 39750 and 43100. When you say: There are more results than expected, you have to decide which one is right.

If you want to see only one of them, then you have to filter more precise or find a more suitable JOIN condition.

Or do you want to see the sum of them? Than you have to aggregate with SUM( ).

chathia
Explorer
0 Kudos

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.

0 Kudos

Hi,

Looks like the query is returning with a cartesian product, can you check your Join Condition.

former_member557244
Participant
0 Kudos

Sir I am unable to sort out where is problem.

Please help me to remove duplication.

Regards

Jörg_Brandeis
Contributor

There is no duplication. The rows are different!

0 Kudos

Thanks, Jorg,

Hello Tariq,

Can you put here the expected results in a table manually for all these rows, just want to see what you are looking to retrieve.