How to join SQL tables and return values that match two possible values?

I've got two tables.

pos.transaction:
- Id (primary key)
- MainframeTransactionSequenceNumber (string)
- TransactionNumber (string)

pos.payment:
- Id (primary key)
- TransactionId (foreign key)
- TenderSubTypeId (int)
- AmountBaseCurrency (decimal)

How can I find out all the TransactionNumbers that have at least two payments where the TenderSubTypeId = 31 and TenderSubTypeId = 37 and both their AmountBaseCurrency < 0?

This query below that I've created returns data where there's at least one of the two TenderSubTypeIds but how can I check for both in a Transaction and return just the TransactionNumber?

select * from pos.[transaction] t
left join pos.[payment] p on t.id = p.transactionid
where (p.TenderSubTypeId = 31 or p.TenderSubTypeId = 37)
and t.AmountTotalBaseCurrency < 0

You can use two HAVING clauses inside a subquery. Each clause counts up the number of payment for a particular TenderSubTypeId. This avoids having to do two lookups on the table.

SELECT
  t.*
FROM pos.transaction t
WHERE EXISTS (SELECT 1
    FROM pos.payment p 
    WHERE t.id = p.transactionid
      AND p.TenderSubTypeId IN (31, 37)
      AND t.AmountTotalBaseCurrency < 0
    HAVING COUNT(CASE WHEN p.TenderSubTypeId = 31 THEN 1 END) > 0
       AND COUNT(CASE WHEN p.TenderSubTypeId = 37 THEN 1 END) > 0
);

You could make it one clause using HAVING COUNT(DISTINCT p.TenderSubTypeId) = 2 but that requires a sort on p.TenderSubTypeId.

You can use two EXISTS predicates that check the related table.

For example:

select *
from transaction t
where exists (select 1 from payment p where p.transactionid = t.id 
              and p.tendersubtype = 31 and amounttotalbasecurrency < 0)
  and exists (select 1 from payment p where p.transactionid = t.id 
              and p.tendersubtype = 37 and amounttotalbasecurrency < 0)

Try to join the pos.[transaction] table with the pos.[payment] table twice, filtering for payments with specific TenderSubTypeId values and negative AmountBaseCurrency. Then group the results by TransactionNumber and select only those groups that have at least two distinct payments, providing the TransactionNumbers that satisfy your condition

SELECT t.TransactionNumber
FROM pos.[transaction] t
JOIN pos.[payment] p1 ON t.Id = p1.TransactionId
JOIN pos.[payment] p2 ON t.Id = p2.TransactionId
WHERE p1.TenderSubTypeId = 31
  AND p1.AmountBaseCurrency < 0
  AND p2.TenderSubTypeId = 37
  AND p2.AmountBaseCurrency < 0
GROUP BY t.TransactionNumber
HAVING COUNT(DISTINCT p1.Id) >= 2