SQL Inner join two tables and extract a boolean value from second column based on condition

Question

I have a table like below. Lets called it Transactions

id   scan_id   amount   date           description
1       123      10     2020-12-01      "tissue"
1       111      500    2020-12-01      "amount to mexico"
1       124      15     2020-12-01      "dairy product"
2       222      1000   2020-13-01      "amount to India"
2       154      10     2020-13-01      "dairy"
3       333      499    2020-14-01      "amt to philipines"
-
-
-
-

I want all transactions where scan_id in (111,222,333) along with a boolean value indicating whether a customer made any other transactions other than scan_id(111,222,333) on the same date. Here scan_ids (111,222,333) refers to Money Tx and others are not. Example required data as below

id   scan_id   amount   date           description            same_day_non_money_tx
1       111      500    2020-12-01      "amount to mexico"        Yes
2       222      1000   2020-13-01      "amount to India"         Yes
3       333      499    2020-14-01      "amt to philipines"       No

I thought of doing an inner join like below

select id, scan_id, amount, date, description
from transactions where scan_id in (111,222,333) as T1
inner join 
select id, scan_id, amount, date, description
from transactions where scan_id not in (111,222,333) as T2
on T1.id = T2.id
and T1.date = T2.date

but this gives me all the transactions which matches based on id and date. How do i get this matching as a separate column?

Answer 1

You can use window functions, if you want the details:

select t.*,
from (select t.*,
             sum(case when scan_id in (111, 222, 333) then 1 else 0 end) over (partition by id, date) as num_123,
             count(*) over (partition by id, date) as num
      from t
     ) t
where cnt <> num_123 and num_123 > 0;