EXISTS with IN returning all rows

Mirithu Kinyanjui 21 Reputation points
2024-02-09T08:57:30.09+00:00

Hi,
I have two tables Table_A and Table_B, both with very many rows. I need data from both so I am using a JOIN and then filtering on column TXNCODE and DVAL in Table_B. Column DVAL has values ONE,TWO,THREE, FOUR,FIVE,SIX and I need only ONE,TWO,THREE,FOUR. I am also filtering where TXNCODE = TX.

I have tried using an IN clause but it is running for a very long time. If I use the EXISTS clause with IN, the TXNCODE filter works (gives only where TXNCODE=TX) but the DVAL returns everything.

Below is the query. Where am I going wrong?

SELECT
    TA.ID,
    TA.DDATE,
    TA.ACCOUNT,
    SUM(TB.AMOUNT) AS TOTAL_AMOUNT,
    TB.TXNCODE,
    TB.DVAL
FROM TABLE_A TA
INNER JOIN TABLE_B TB ON TA.ID = TB.ID
WHERE
    TA.DDATE = @SDATE
AND
    TB.TXNCODE = 'TX'
AND
    EXISTS (
        SELECT 1
        FROM TABLE_B TB2
        WHERE TA.ID = TB2.ID
        AND TB2.DVAL IN ('ONE', 'TWO', 'THREE', 'FOUR')
    )
GROUP BY
    TA.ID,
    TA.DDATE,
    TA.ACCOUNT,
    TB.TXNCODE,
    TB.DVAL;
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes