Search code examples
mysqlsqlspringselectdistinct

How to Remove duplicates with Select Distinct and Left Join in Spring Framework

I have two tables, namely FUTURE_DATED_TRANSACTION and then TRANSACTION_JOURNAL . I think I kinda know how to filter if records exist on one table only, however I can't quite do it in tandem with a left join (2nd table).

My TRANSACTION_JOURNAL table has a FUTURE_DATED_TRANSACTION_ID field which contains duplicate values and I want it be gone from the output as it just output duplicates from the parent table as could be seen in the sample below.

My current output is like this

FUTURE_DATED_TRANSACTION_1

  • TRANSACTION_JOURNAL_1
  • TRANSACTION_JOURNAL_1
  • TRANSACTION_JOURNAL_1

My expected output would be

FUTURE_DATED_TRANSACTION_1

  • TRANSACTION_JOURNAL_1

How can I make it output that way?

My code snippet is

private final DSLContext dsl;
........
    
return this.dsl
        .select(
            FUTURE_DATED_TRANSACTION.FUTURE_DATED_TRANSACTION_ID,
            FUTURE_DATED_TRANSACTION.REFERENCE_NUMBER,
            FUTURE_DATED_TRANSACTION.RECURRING_FREQUENCY,
            FUTURE_DATED_TRANSACTION.RECURRING_DAY_OF_THE_MONTH,
            FUTURE_DATED_TRANSACTION.RECURRING_DAY_OF_THE_WEEK,
            FUTURE_DATED_TRANSACTION.TRANSFER_SCHEDULE_DATE,
            FUTURE_DATED_TRANSACTION.RECURRING_START_DATE,
            FUTURE_DATED_TRANSACTION.RECURRING_END_DATE,
            FUTURE_DATED_TRANSACTION.AMOUNT,
            FUTURE_DATED_TRANSACTION.STATUS,
            FUTURE_DATED_TRANSACTION.STATUS_REMARKS,
            FUTURE_DATED_TRANSACTION.CURRENCY_ID,
            TRANSACTION_JOURNAL.ID.as("transactions_id"),
            TRANSACTION_JOURNAL.REFERENCE_NUMBER.as("transactions_childReferenceNumber"),
            TRANSACTION_JOURNAL.SOURCE_ACCOUNT_NUMBER.as("transactions_debtorAccountId"),
            TRANSACTION_JOURNAL.SOURCE_ACCOUNT_TYPE.as("transactions_debtorAccountType"),
            TRANSACTION_JOURNAL.DESTINATION_ACCOUNT_NUMBER.as("transactions_creditorAccountId"),
            TRANSACTION_JOURNAL.DESTINATION_ACCOUNT_TYPE.as("transactions_creditorAccountType"),
            TRANSACTION_JOURNAL.CURRENCY_ID.as("transactions_debtorAccountCurrencyId"),
            TRANSACTION_JOURNAL.CURRENCY_ID_DESTINATION.as("transactions_creditorAccountCurrencyId"),
            TRANSACTION_JOURNAL.AMOUNT.as("transactions_amount"),
            TRANSACTION_JOURNAL.SERVICE_FEE.as("transactions_serviceFee"),
            TRANSACTION_JOURNAL.TRANSACTION_DATE.as("transactions_transferScheduledDate"),
            TRANSACTION_JOURNAL.ESB_POSTED_DATE.as("transactions_postedDate"),
            TRANSACTION_JOURNAL.STATUS.as("transactions_transferStatus"),
            TRANSACTION_JOURNAL.STATUS_REMARKS.as("transactions_statusRemarks"),
            TRANSACTION_JOURNAL.TRANSFER_SCHEDULE_TYPE.as("transactions_transactionType"),
            TRANSACTION_JOURNAL.REMARKS.as("transactions_remarks"),
            TRANSACTION_JOURNAL.POSTING_DATE.as("transactions_completionDate"),
            TRANSACTION_JOURNAL.CREDITOR_DETAILS.as("transactions_creditorDetails"),
            SEND_MONEY_TYPE.CODE.as("transactions_sendMoneyCode"),
            SEND_MONEY_TYPE.SEND_MONEY_TYPE_NAME.as("transactions_sendMoneyType")
        )
        .from(
            this.dsl
                .select(FUTURE_DATED_TRANSACTION.fields())
                .from(FUTURE_DATED_TRANSACTION)
                .leftJoin(TRANSACTION_JOURNAL)
                .on(FUTURE_DATED_TRANSACTION.FUTURE_DATED_TRANSACTION_ID
                    .eq(TRANSACTION_JOURNAL.FUTURE_DATED_TRANSACTION_ID))
                .where(this.getQueryConditions(sendMoneyScheduledFilter))
                .and(this.addWildCardQueryParent(query))
                .and((TRANSACTION_JOURNAL.DESTINATION_ACCOUNT_NUMBER.in(accountList)
                    .and(TRANSACTION_JOURNAL.STATUS.eq(TransactionStatus.POSTED.toLabel())))
                    .or(TRANSACTION_JOURNAL.CUSTOMER_NUMBER.eq(userId)))
                .groupBy(FUTURE_DATED_TRANSACTION.fields())
                .orderBy(FUTURE_DATED_TRANSACTION.FUTURE_DATED_TRANSACTION_ID.desc())
                .limit(limit)
                .offset(offset - 1)
                .asTable("future_dated_transaction")
        )
        .leftJoin(TRANSACTION_JOURNAL)
        .on(FUTURE_DATED_TRANSACTION.FUTURE_DATED_TRANSACTION_ID
            .eq(TRANSACTION_JOURNAL.FUTURE_DATED_TRANSACTION_ID))
        .leftJoin(SEND_MONEY_TYPE)
        .on(TRANSACTION_JOURNAL.SEND_MONEY_TYPE_ID.eq(SEND_MONEY_TYPE.ID))
        .where(this.getQueryConditions(sendMoneyScheduledFilter));

I already tried different iterations of Select Distinct like the one below,

enter image description here

enter image description here enter image description here

but I kept getting an error of

.....nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on (transaction_journal.future_dated_transaction_id) `future_dated_transacti' at line 1"

this is almost the same I am getting across my different code iterations of using Select Distinct. Is this sorta not supported with my current setup really or just my code is wrong?. TIA.

Solution

  • Oh nevermind, after a whole day of trying to iterate between select distint queries. I gave up and tried clicking everything and checking what intelisense says that is available for me and here I found that groupBy is the one that would work for me. I was soo focused on using Select Distinct as this is the SQL command I know that would work for my usecase. Apparently there's another way, or prolly the only way. If anyone could recommend me a way to do this with Select Distinct Query, I'd marked that one as an answer. In the meantime, heres what I did (just see the full code snippet from the question above, I truncated it to lessen the length of code here) :)

    ..................

.leftJoin(TRANSACTION_JOURNAL)
            .on(FUTURE_DATED_TRANSACTION.FUTURE_DATED_TRANSACTION_ID
                .eq(TRANSACTION_JOURNAL.FUTURE_DATED_TRANSACTION_ID))
            .leftJoin(SEND_MONEY_TYPE)
            .on(TRANSACTION_JOURNAL.SEND_MONEY_TYPE_ID.eq(SEND_MONEY_TYPE.ID))
            .where(this.getQueryConditions(sendMoneyScheduledFilter))
            .groupBy(TRANSACTION_JOURNAL.FUTURE_DATED_TRANSACTION_ID);