Solution for SQL Join queries on related data
is Given Below:
I have data table in SQL Server that looks like the following Data Table
The data table has Columns TranID nvarchar(50), Amt decimal(38,2), IsCancelled bit.
If a transaction is called, the column named IsCancelled has boolean value 0, but when the transaction is called, a duplicate record is inserted with the column IsCancelled set to 1. I need to write a query that will return records only when a row does not have a corresponding cancelled transaction. In my case, only the record with TranID XYZ should appear be returned from the query. The record with TranID ABC should not be returned since it has a corresponding IsCancelled with bollean value 1. I could run a cursor query for records with IsCancelled set to 0 and use it to determine if there is any corrosponding IsCancelled = 1 and then discard the record, but this looping multiple records causes delay. Is there a way I can write a single query to handle this task?
You can join the table to itself like this:
SELECT * FROM MYTABLE transaction LEFT JOIN MYTABLE cancelled ON (transaction.TranID = cancelled.TranID AND transaction.Amt = cancelled.Amt AND cancelled.isCancelled = 1) WHERE transaction.isCancelled = 0 AND cancelled.TranID IS NULL
If your it is guaranteed that if your table contains two rows with same
TranId it means that it was cancelled, the query can be simplified to
SELECT TranId, Amt, 0 AS isCancelled FROM MYTABLE transaction GROUP BY TranId HAVING COUNT(isCancelled) = 1
Creating index on
TranID is advised for both scenarios