SQL change subquery to join

I have the following query, which has a subquery. I need the subquery converted to a join with a separated-out “where” clause for the dates, which will be parametised in an excel spreasheet.

    select me.id, me.merchant_num, me.merchant_nm,
    (select count(1) from transaction_t where merchant_id = me.id and transaction_dt BETWEEN '2020-04-01' and '2020-04-30') as num_transactions
    FROM merchant_t me
    left outer join transaction_t tt on tt.merchant_id = me.id
    where me.status = 'T'

The subquery obtains a count of all the merchant’s transactions between given dates. I’ve tried everything I can think of but I either get too many rows or some other thing is wrong.

There are two tables involved:

merchant_t
----------
id     merchant_num    merchant_nm    status


transaction_t
--------------
id     merchant_id     transaction_dt

Answer

This should work :

    SELECT me.id, me.merchant_num, me.merchant_nm, count(1) as num_transactions
    FROM merchant_t me
    LEFT OUTER JOIN transaction_t
         ON t.merchant_id = me.id 
         AND t.transaction_dt BETWEEN '2020-04-01' and '2020-04-30'
    WHERE me.status = 'T'
    GROUP BY me.id, me.merchant_num, me.merchant_nm