How do I remove duplicates in a several join query?

Question

0.00/5 (No votes)

See more:

I am getting more rows then I expect returned by my query.

I believe it has something to do with my join statements.

There are multiple tables that have different information in them from SAGE X3 System

SQL

SELECT distinct
    GL.LEDTYP_0 AS 'Ledger Type',
    APL.LANMES_0 AS 'Ledger_Type_Description',
    GL.LED_0 AS 'Ledger_Code',
    A9.TEXTE_0 AS 'Ledger_Code_Description',
    GL.COA_0 AS 'Chart_Of_account_Code',
    A10.TEXTE_0 AS 'Chart_Acct_Description',
    Company.CPYNAM_0 AS 'Company_Description',
    GL.FIY_0 AS Fiscal_Year,
    GL.CPY_0 AS 'Company_Code',
    GL.PER_0 AS PERIOD,
    
	GL.OFFACC_0 AS 'Partner_Code',
	Partner.BPRNAM_0 AS 'Partner_Description',
	GL.ACC_0 AS 'Account Code', 
	A11.TEXTE_0 AS 'Account Description',
	GL.NUM_0 AS 'Document_Number', 
	GL.DES_0 AS 'Document Description',
	GL.CCE_0 'Value Stream', 
	A.TEXTE_0 AS 'Value Stream Name',
	GL.CCE_1 AS 'Product_Line',
	A1.TEXTE_0 AS 'Product_Line_Name',
	GL.CCE_2 AS 'CropYear',
	A2.TEXTE_0 AS 'CropYear Name',
	GL.CCE_3 AS Project,
	A3.TEXTE_0 AS 'ProjectName',
	GL.CCE_4 AS Department,
	A4.TEXTE_0 AS Department_Name,
	GL.ACCDAT_0 AS AccountingDate, 
	Format(GL.PSTLED_0, 'C') AS 'Ledger_Posting',
	
	GL.CAT_0 AS 'Journal_Category',
	GL.NEWFIYNUM_0 AS 'NewFiscalYear',
	GL.NEWPERNUM_0 AS 'NewFiscalPeriod'
	
FROM
    [SEI_LIVE].[GL_TX_WITH_OB_VIEW_SEP_AUG] GL 
    LEFT JOIN
        COMPANY Company 
        ON GL.CPY_0 = Company.CPY_0 
    LEFT JOIN
        GACCOUNT GA 
        ON GL.ACC_0 = GA.ACC_0 
    LEFT JOIN
        PINVOICED INVOICED 
        ON GL.NUM_0 = INVOICED.NUM_0 
    LEFT JOIN
        APLSTD APL 
        ON GL.LEDTYP_0 = APL.LANNUM_0 
        AND APL.LANCHP_0 = '2644' 
        AND LAN_0 = 'ENG' 
    LEFT JOIN
        BPARTNER Partner 
        ON GL.OFFACC_0 = Partner.BPRNUM_0 
    LEFT JOIN
        ATEXTRA A10 
        ON GL.COA_0 = A10.IDENT1_0 
        AND A10.CODFIC_0 = 'GCOA' 
        AND A10.ZONE_0 = 'DESTRA' 
        AND A10.LANGUE_0 = 'ENG' 
    LEFT JOIN
        ATEXTRA A11 
        ON GL.COA_0 = A11.IDENT1_0 
        AND GL.ACC_0 = A11.IDENT2_0 
        AND A11.CODFIC_0 = 'GACCOUNT' 
        AND A11.ZONE_0 = 'DESTRA' 
        AND A11.LANGUE_0 = 'ENG' 
    LEFT JOIN
        ATEXTRA A 
        ON GL.DIE_0 = A.IDENT1_0 
        AND GL.CCE_0 = A.IDENT2_0 
        AND A.CODFIC_0 = 'CACCE' 
        AND A.ZONE_0 = 'DESTRA' 
        AND A.LANGUE_0 = 'ENG' 
    LEFT JOIN
        ATEXTRA A1 
        ON GL.DIE_1 = A1.IDENT1_0 
        AND GL.CCE_1 = A1.IDENT2_0 
        AND A1.CODFIC_0 = 'CACCE' 
        AND A1.ZONE_0 = 'DESTRA' 
        AND A1.LANGUE_0 = 'ENG' 
    LEFT JOIN
        ATEXTRA A2 
        ON GL.DIE_2 = A2.IDENT1_0 
        AND GL.CCE_2 = A2.IDENT2_0 
        AND A2.CODFIC_0 = 'CACCE' 
        AND A2.ZONE_0 = 'DESTRA' 
        AND A2.LANGUE_0 = 'ENG' 
    LEFT JOIN
        ATEXTRA A3 
        ON GL.DIE_3 = A1.IDENT1_0 
        AND GL.CCE_3 = A1.IDENT2_0 
        AND A3.CODFIC_0 = 'CACCE' 
        AND A3.ZONE_0 = 'DESTRA' 
        AND A3.LANGUE_0 = 'ENG' 
    LEFT JOIN
        ATEXTRA A4 
        ON GL.DIE_4 = A4.IDENT1_0 
        AND GL.CCE_4 = A4.IDENT2_0 
        AND A4.CODFIC_0 = 'CACCE' 
        AND A4.ZONE_0 = 'DESTRA' 
        AND A4.LANGUE_0 = 'ENG'
	
    LEFT JOIN
        ATEXTRA A9 
        ON GL.LED_0 = A9.IDENT1_0 
        AND A9.CODFIC_0 = 'GLED' 
        AND A9.ZONE_0 = 'DESTRA' 
        AND A9.LANGUE_0 = 'ENG' 
WHERE
    ---GL.ACC_0 = '17000' 
     YEAR(GL.ACCDAT_0) > 2019

What I have tried:

I have tried Row number technique I not good at subqueries at all

Posted 14-Jun-21 4:49am

jeannie ramirez

Updated 14-Jun-21 5:06am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-06-14T05:06:00

Without sample data it's almost impossible for us to give you a definitive answer but here are some techniques for you to try

1.Try returning just a few of the columns from [SEI_LIVE].[GL_TX_WITH_OB_VIEW_SEP_AUG]E.g.

SQL

SELECT distinct
    GL.LEDTYP_0 AS 'Ledger Type',
    GL.LED_0 AS 'Ledger_Code',
    GL.FIY_0 AS Fiscal_Year,
    GL.CPY_0 AS 'Company_Code',
    GL.PER_0 AS PERIOD
FROM
    [SEI_LIVE].[GL_TX_WITH_OB_VIEW_SEP_AUG] GL
ORDER BY GL.LEDTYP_0

That will presumably return the expected number of rows

2. Then try adding just the first JOIN

SQL

LEFT JOIN
    COMPANY Company
    ON GL.CPY_0 = Company.CPY_0

and see what that does to the row count. Add the joins in one-by one until you start getting the extra rows.

3. Now you need to examine the data. Are you missing something in the ON Clause that is causing the extra rows? Is the data duplicated on that table and needs fixing? Etc Etc. Without your data I have to be a bit vague here - sorry.

If you are not comfortable with sub-queries try Common Table Expressions[^] which might be easier to follow. For example that point 1 above could become

SQL

;with cte as
(
    SELECT 
    GL.LEDTYP_0 AS 'Ledger Type',
    GL.LED_0 AS 'Ledger_Code',
    GL.FIY_0 AS Fiscal_Year,
    GL.CPY_0 AS 'Company_Code',
    GL.PER_0 AS PERIOD
    FROM
    [SEI_LIVE].[GL_TX_WITH_OB_VIEW_SEP_AUG] GL
) select * from cte
ORDER BY 'Ledger Type'

If you follow the steps I've suggested above, then you could introduce ROW_NUMBER() based on an appropriate PARTITION BY inside the cte. then use

SQL

select * from cte where [row number] = 1;

Wendelius · Answer 2 · 2021-06-14T05:06:00

Without seeing the actual data you have and the result along with the expected it's quite impossible to say what is wrong with the query, or the data.

However, since you have already used DISTINCT you won't have duplicates in the result. So the next step would be to investigate the data you receive and see what is different between the rows in a set where you would expect only one row. This helps you to identify which join produces unwanted rows.

How do I remove duplicates in a several join query?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0