Getting Duplicates in the output

[Albatross]

Hi there,

I wrote and executed the below query but it is fetching duplicate values as well. Kindly let me know what changes i need to do to get the unique results. I mean am i using any wrong tables in joining condition. I even tried INNER join still getting the same... PFA 

QUERY I USED :

select DISTINCT top(2000) MARA.MATNR, BKPF.BUKRS, MSEG.WERKS, T001L.LGORT, BKPF.GJAHR, MSEG.BWART,
MKPF.XBLNR,
MSEG.DMBTR,
MSEG.MENGE,
MKPF.BUDAT,
MKPF.XABLN,
MBEW.PEINH,
MARD.LABST,
T156T.BTEXT
FROM MARA
INNER  JOIN MSEG ON MSEG.MANDT=MARA.MANDT AND MSEG.MATNR=MARA.MATNR
INNER  JOIN BKPF ON BKPF.MANDT = MSEG.MANDT AND BKPF.BUKRS = MSEG.BUKRS
INNER  JOIN T001L ON T001L.MANDT = MSEG.MANDT AND T001L.WERKS = MSEG.WERKS AND T001L.LGORT = MSEG.LGORT
INNER  JOIN MKPF ON MKPF.MANDT = BKPF.MANDT AND MKPF.MBLNR = BKPF.BELNR
INNER  JOIN MBEW ON MBEW.MANDT=MSEG.MANDT AND MBEW.BWTAR=MSEG.BWTAR
INNER   JOIN MARD ON MARD.MANDT=MBEW.MANDT AND MARD.LFGJA = MBEW.LFGJA
INNER JOIN T156T ON T156T.MANDT=MSEG.MANDT  AND T156T.KZZUG=MSEG.KZZUG
WHERE MARA.MANDT ='500' AND
MARA.MATNR='1L90MBUS3.6/250' AND MSEG.WERKS='VNSD'AND BKPF.GJAHR='2022'  AND T156T.SPRAS='E';

[Sandra_Rossi]

Please format your code with button "..." and "</>".

Please embed the screenshots e.g. copy/paste

[Albatross]

Hi @Sandra, Sorry i'm new to this community. I couldn't get u. And i didn't see any copy option that's y i added the pic of the output.

[Sandra_Rossi]

I'm also new, because the forum is new (February 2024). It's why I spent some time to understand all available features before posting my questions.

Buttons:

Copy = Ctrl+C (of your screenshot via your preferred screenshot software)

Paste in the forum = Ctrl+V

[Albatross]

Hey hi @Sandra i couldn't found the code option under comment section so i have done what u suggested in ANSWER box...kindly go through that code once and let me know whether am i using wrong combination of SAP tables that gives me duplicates. Thanks in advance

[Sandra_Rossi]

You can find by yourself why the query gives duplicate lines, by removing one table at a time till you get unique lines: it's the last table removed which makes the duplicate lines.

[Johan_H]

Hi,

T156T.BTEXT is the only field that is different for each record. So technically speaking you are not getting duplicate records.

Question is, are all the BTEXT values correct for the "duplicated" record?
If they are, you could look into STUFF and FOR XML to combine all values into one row.
If they are not, this "INNER JOIN T156T ON T156T.MANDT=MSEG.MANDT  AND T156T.KZZUG=MSEG.KZZUG" needs another parameter.

Regards,

Johan

[Albatross]

select DISTINCT TOP(2000) MARA.MATNR,BKPF.BUKRS,MSEG.WERKS,T001L.LGORT,BKPF.GJAHR,MSEG.BWART,

MKPF.XBLNR,

MSEG.DMBTR,

MSEG.MENGE,

MKPF.BUDAT,

MKPF.XABLN,

MBEW.PEINH,

MARD.LABST,

T156T.BTEXT

FROM MARA

INNER JOIN MSEG ON MSEG.MANDT=MARA.MANDT AND MSEG.MATNR=MARA.MATNR

INNER JOIN BKPF ON BKPF.MANDT = MSEG.MANDT AND BKPF.BUKRS = MSEG.BUKRS

INNER  JOIN T001L ON T001L.MANDT = MSEG.MANDT AND T001L.WERKS = MSEG.WERKS AND T001L.LGORT = MSEG.LGORT

INNER JOIN MKPF ON MKPF.MANDT = BKPF.MANDT AND MKPF.MBLNR = BKPF.BELNR

INNER  JOIN MBEW ON MBEW.MANDT=MSEG.MANDT AND MBEW.BWTAR=MSEG.BWTAR

INNER  JOIN MARD ON MARD.MANDT=MBEW.MANDT AND MARD.LFGJA = MBEW.LFGJA

INNER JOIN T156T ON T156T.MANDT=MSEG.MANDT  AND T156T.KZZUG=MSEG.KZZUG

WHERE MARA.MANDT ='500' AND

MARA.MATNR='1L90MBUS3.6/250' AND MSEG.WERKS='VNSD'AND BKPF.GJAHR='2022'  AND T156T.SPRAS='E';

[Sandra_Rossi]

Thank you. For information, you have a menu "Edit Question". Here, what you did is to post a solution.