CODEWITHSUNDEEP
sql
Sam
Sam

Reputation: 1

SQL select where matching record exists and also when there is no matching record

I am trying to return Account data from one table and financial Transaction date from another table. My existing query returns all the data I need when an Account has a matching financial Transaction, but I also need to return records (groups) from the Account table where there are no matching financial transactions...either because there are no financial transactions to begin with or because I am only looking for a certain type of financial transaction.

Here is my existing query and the results. I am also expecting (or trying) to see a row for each PaymentVendor where there have been no Collections. SELECT t.PaymentVendor, COUNT(t.AccountId) AS Accounts, SUM(t.TransactionAmount \* -1) AS Collections from dbo.RawTransactionData t JOIN dbo.RawAccountData a on t.AccountId = a.AccountId WHERE t.TransactionDate \>= a.AddressDate AND t.TransactionType = 'FOR' AND t.TransactionStatus = 'U' GROUP BY t.PaymentVendor

query results

Here is how my query started:

SELECT t.paymentvendor, t.accountid, t.transactiondate, 
t.transactionamount, t.transactionstatus, t.transactiontype
FROM dbo.RawTransactionData t
JOIN dbo.RawAccountData a on a.accountid = t.accountid
WHERE t.TransactionType = 'FOR'
AND t.TransactionStatus = 'U'
AND a.AddressDate IS NOT NULL
AND t.TransactionDate > a.AddressDate

I am hoping to find a way to return all account records that meet the criteria (even if there are no matching transactions)

Upvotes: 0

Views: 150

Answers (1)

tinazmu
tinazmu

Reputation: 5139

You can use LEFT JOIN in these scenarios, the LEFT table being the one you want all rows included:

SELECT t.PaymentVendor, COUNT(t.AccountId) AS Accounts, SUM(t.TransactionAmount * -1) AS Collections 
from 
   dbo.RawAccountData a 
   LEFT JOIN 
   dbo.RawTransactionData t 
   on t.AccountId = a.AccountId 
      AND  t.TransactionDate >= a.AddressDate 
      AND t.TransactionType = 'FOR' 
      AND t.TransactionStatus = 'U' 
GROUP BY t.PaymentVendor

Upvotes: 1

Related Questions