CODEWITHSUNDEEP
sqlsql-server
user460114
user460114

Reputation: 1848

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

Upvotes: 1

Views: 40

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Your query is actually fine . . . almost. You have an unnecessary JOIN in the outer query:

select me.id, me.merchant_num, me.merchant_nm,
      (select count(1)
       from transaction_t
       where t.merchant_id = me.id and
             t.transaction_dt between '2020-04-01' and '2020-04-30'
     ) as num_transactions
from merchant_t me
where me.status = 'T';

Although you ask for a left join, this version is likely to have better performance (with an index on transact_t(merhant_id, transaction_dt) because it avoids the outer aggregation.

Upvotes: 1

Olivier Depriester
Olivier Depriester

Reputation: 1625

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

Upvotes: 2

Jim Macaulay
Jim Macaulay

Reputation: 5141

Please use below query,

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

You can also use below query,

select me.id, me.merchant_num, me.merchant_nm, qry.num_transactions, count(1) from merchant_t me
 left outer join transaction_t qry
on (me.id = qry.merchant_id)
where me.status = 'T'
group by me.id, me.merchant_num, me.merchant_nm, qry.num_transactions;

Upvotes: 2

mkRabbani
mkRabbani

Reputation: 16908

You can try this below logic-

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

Upvotes: 2

Related Questions