SQL server: Removing results based on keyword match

Question

all.

I have a database of financial transactions where accounts can have multiple rows. Call it TableA.

I have another database of daily rep activities where accounts can have multiple rows. This is TableB.

When an account in TableA has a certain transaction type without a certain activity type in TableB, it is in violation and needs to appear on a report.

The correct activity type in TableB contains the string "%roll%" in the Title field. So I want to show all accounts that have transaction type X in TableA but no activity Title containing "%roll%" in TableB.

The issue I'm having is that when accounts in TableB have activity type "%roll%", which makes it a good account, if the account has other activity type(s) it shows up in my results set. How do I exclude the good accounts from showing up in the results at all?

Here is current query:

SELECT distinct
DATEDIFF(DAY,GETDATE(),a.transaction_dt) as 'Aging'
,b.RECORD_OPENED_DT
,a.branch_cd
,a.account_cd
,a.branch_cd+a.account_cd as 'FULL_ACCT'
,b.ADT_CUSTOMER_CD as 'ACCT_TYPE'
,c.FIRST_NM
,c.LAST_NM
,c.COMPANY_NM
,a.rr_cd
,e.RR_NM
,f.ADDRESS_EMAIL_TXT
,i.activityEndTime
,i.title
,a.batch_cd
,a.entry_cd
,a.processing_dt
,a.transaction_dt
,a.tran_total_amt 

FROM (SELECT branch_cd, account_cd, rr_cd, batch_cd, entry_cd, processing_dt, transaction_dt, tran_total_amt
    FROM TableA 
        WHERE BRANCH_CD > '299'
        and BRANCH_CD < '400'
        and batch_cd = 'wt'
        and (entry_cd like 'cd%'
            or entry_cd like 'ca%'
            or entry_cd like 'ce%'
            or entry_cd like 'cb%'
            or entry_cd like 'rft')
        and PROCESSING_DT > '2017-04-01 00:00:00.000'
        and TRAN_TOTAL_AMT > '0.00') a



left join Table0 b
    on a.BRANCH_CD = b.BRANCH_CD
    and a.ACCOUNT_CD = b.ACCOUNT_CD
    and b.ADT_CUSTOMER_CD in ('120','121','122','122','123','124','125','126','129','130','131','132','133','134','135','139','140','141','142','143',
        '150','151','160','161','170','171','172','180','181','182','183','184','185','186','187','188','189')


left join Table1 c
    on a.BRANCH_CD = c.BRANCH_CD
    and a.ACCOUNT_CD = c.ACCOUNT_CD
    and AP_SEQ_NBR = '1'

left join Table2 d
    on a.BRANCH_CD = b.BRANCH_CD
    and a.ACCOUNT_CD = b.ACCOUNT_CD
    and (CASE WHEN a.BRANCH_CD < '699' THEN '000'
                ELSE a.BRANCH_CD
                END)+a.RR_CD = (d.BRANCH_CD+d.RR_CD)

left join Table3 e
    on a.BRANCH_CD = b.BRANCH_CD
    and a.ACCOUNT_CD = b.ACCOUNT_CD
    and d.CRD_NBR = e.CRD_NBR

left join Table4 f
    on a.BRANCH_CD = b.BRANCH_CD
    and a.ACCOUNT_CD = b.ACCOUNT_CD
    and (CASE WHEN a.BRANCH_CD < '699' THEN '000'
                ELSE a.BRANCH_CD
                END)+a.RR_CD = (f.BRANCH_CD+f.RR_CD)

left join Table5 g
    on (a.branch_cd+a.account_cd) = g.accountnumber


left join Table6 h
    on (a.branch_cd+a.account_cd) = g.accountnumber
        and g.primaryownerid = h.entityId

right join (SELECT id, classcode, statuscode, title, activityEndTime
            FROM TableB
        where classCode = 'doc'
        and statusCode = 'comp'
        and title not like '%roll%') i
    on (a.branch_cd+a.account_cd) = g.accountnumber
        and g.primaryownerid = h.entityId
        and h.actId = i.id

Can you show us the tables schemas and a Sample data? – Maurício Pontalti Neri May 31 '17 at 19:48 — Maurício Pontalti Neri, May 31 '17 at 19:48
Updated the main post. – Jodi Rehlander May 31 '17 at 20:15 — Jodi Rehlander, May 31 '17 at 20:15

Paurian · Answer 1 · 2017-05-31T21:00:56.470

Your question is a bit generic - so I'll provide a rather generic answer.

There is an "IN" predicate that you can invert with NOT. Something like:

SELECT
  ta.*
FROM
  TableA AS ta
WHERE
  ta.accountId NOT IN ( SELECT tb.accountId FROM TableB AS tb WHERE title LIKE '%roll%' )

Using the NOT IN concept, it would fit here in your query:

left join Table6 h
    on (a.branch_cd+a.account_cd) = g.accountnumber
        and g.primaryownerid = h.entityId
        AND h.actId NOT IN ( SELECT id FROM TableB WHERE title LIKE '%roll%' ) -- This Line ensures that account IDs are only allowed from table6 if they aren't related to infractions from tableB.

In general, if you don't need to display information from TableB, don't include it in your FROM, but use it as a filter within your WHERE or JOIN. Bringing in data increases the work the server needs to do. If, however, your query needs data from TableB and TableA, by all means JOIN them without an IN predicate. It would be helpful if you exposed a scrubbed schemata or provided some example queries.

Thanks for adding your work - it's still a little lacking, but I think I get the gist enough to help. Moving forward, when you post on Stack Overflow, start with showing examples of the problem and code for the attempts that you've tried so you don't get down voted. FWIW - I don't downvote questions, just answers if they're trolling.

Let me know if this direction helps you out or not. There are issues with performance that can be addressed in other threads, but this should come closer to getting you the results you're looking for.

As I commented on @Ben's reply, I used a subquery with an inner join to create the list of "good" accounts and then used the "NOT IN" to find the bad ones. You helped get me on the right track! — Jodi Rehlander, Jun 01 '17 at 17:43

score 0 · Answer 2 · answered May 31 '17 at 20:00

0

If you want to use a join:

SELECT * -- just the rows you need
FROM TableA
LEFT JOIN TableB on TableB.Id = TableA.Id
WHERE TableA.TransactionType = "Something" and 
TableB.ActivityType not like '%roll%'

answered May 31 '17 at 20:00

Ben

1,820
2
14
25

I added my current code to the main post. I basically did what you suggest but the problem is that if TableB has ActivityType in addition to "%roll%", I get those rows when what I want is to not list that account at all. – Jodi Rehlander May 31 '17 at 20:29
Does the right join need to be a left join? Check out Daan Timmer's answer here: https://stackoverflow.com/questions/4715677/difference-between-left-join-and-right-join-in-sql-server – Ben May 31 '17 at 20:58
Actually I used a subquery with an inner join to create the list of "good" accounts and then used the "NOT IN" to find the bad ones. You helped get me on the right track! – Jodi Rehlander Jun 01 '17 at 17:42

SQL server: Removing results based on keyword match

2 Answers2