CODEWITHSUNDEEP
sqlsql-server
az6bcn
az6bcn

Reputation: 339

SQL subquery filter using parent table fields

I have a query as below:

 SELECT tr.AccountFK ,
          TransactionRequestStatus = CASE
            WHEN tr.TransactionRequestStatusFK  = 2 THEN 'Accepted' 
            ELSE 'Rejected'
        END,
        tr.TransactionRequestID

         FROM [CRM].[acc].[TransactionRequest] as tr
         INNER JOIN CRM.acc.TransactionRequestHistory as trh
         ON tr.TransactionRequestID = trh.TransactionRequestFK
         where tr.TransactionRequestTypeFK = 3 --bill split
         and TransactionRequestStatusFK in (2, 3) --Approved, Rejected  

         --Filter- on each item
         and (tr.TransactionRequestStatusFK = 2 and tr.TransactionRequestID not in (select tr.TransactionRequestID from crm.acc.TransactionRequest tr
                                    INNER JOIN CRM.acc.TransactionRequestHistory as trh
                                    ON tr.TransactionRequestID = trh.TransactionRequestFK
                                    where tr.TransactionRequestID = trh.TransactionRequestFK
                                    and trh.EventFK = 15)
                or
                tr.TransactionRequestStatusFK = 3 and  tr.Notes not like '%No Action%')  
        group by tr.AccountFK, tr.TransactionRequestStatusFK, tr.TransactionRequestID

I have a subquery in the where clause to apply a filter on each item but this subquery join is the same has the join in the main query, is there any way to re-use the main query table and avoid repeating same code in the subquery.

Upvotes: 1

Views: 768

Answers (1)

Ajay Gupta
Ajay Gupta

Reputation: 1845

Using CTE. Assuming the column names in both crm.acc.TransactionRequest and CRM.acc.TransactionRequestHistory are distinct, the following should work. If they aren't, then you'll have to specify the columns with their alias in the Select of cte.

with cte as
(select * from crm.acc.TransactionRequest tr
                                    INNER JOIN CRM.acc.TransactionRequestHistory as trh
                                    ON tr.TransactionRequestID = trh.TransactionRequestFK)

SELECT AccountFK ,
          TransactionRequestStatus = CASE
            WHEN TransactionRequestStatusFK  = 2 THEN 'Accepted' 
            ELSE 'Rejected'
        END,
        TransactionRequestID

         FROM cte
         where TransactionRequestTypeFK = 3 --bill split
         and TransactionRequestStatusFK in (2, 3) --Approved, Rejected  

         --Filter- on each item
         and (TransactionRequestStatusFK = 2 and TransactionRequestID not in 
         (select TransactionRequestID from cte
                                      where TransactionRequestID = TransactionRequestFK
                                      and EventFK = 15)
                or
                TransactionRequestStatusFK = 3 and  Notes not like '%No Action%')  
        group by AccountFK, TransactionRequestStatusFK, TransactionRequestID

Upvotes: 1

Related Questions