CODEWITHSUNDEEP
sqlsql-server
Johnny Bones
Johnny Bones

Reputation: 8404

SQL to find only data in a table with no matching records

I would like to find data in a table with no matching records based on two fields (ContractState, TransID).

For instance, assume this data set (in reality, all data sets contain hundreds of records, I'm just including a few):

AccountNbr  ContractState   TransID     Product
3335477     AL              80079       DPPO, DHMO
3335477     AL              80080       PPO
3335477     AR              80079       DPPO, DHMO
3335477     AR              80080       PPO

This should return 0 records, because there are 2 records for AL (one for each TransID) and 2 records for AR.

However, given the following data set:

AccountNbr  ContractState   TransID     Product
3335477     AL              80079       DPPO, DHMO
3335477     AL              80080       PPO
3335477     DE              80079       DHMO
3335477     WA              80080       DHMO

I would like to return only the following data set:

AccountNbr  ContractState   TransID     Product
3335477     DE              80079       DHMO
3335477     WA              80080       DHMO

because for each state, there is only 1 TransID.

I have this code, but it also includes records with matching data:

SELECT 
   'tblSQLContractState' as TableName, 
   TransID, 
   ContractState, 
   Product, 
   COUNT(*) AS [NumOfMessage]
FROM tblSQLContractState  
WHERE   TransID IN (80079, 80080)
GROUP BY 
   TransID, 
   ContractState, 
   Product
HAVING COUNT(*) = 1

Upvotes: 0

Views: 66

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

Your query is close. But you want one result row per ContractState - and it's also this attribute you want to know the number of records (aggregate COUNT(*)) for. So remove Product and TransID from the GROUP BY clause.

As it's only combinations with a row count of 1 you want to show, you can use MIN(Product) or MAX(Product) to get the one product in question. Same for TransID.

SELECT 
   'tblSQLContractState' as TableName, 
   MAX(TransID) AS TransID, 
   ContractState, 
   MAX(Product) AS Product, 
   COUNT(*) AS [NumOfMessage]
FROM tblSQLContractState  
WHERE   TransID IN (80079, 80080)
GROUP BY ContractState
HAVING COUNT(*) = 1;

Gordon's answer is more more straight-forward and more of general use. Just the natural way to solve this in my opinion. I simply wanted to show that your query was very close :-)

UPDATE: Tim's answer is also very good. (It wasn't there when I typed mine.) He is right; all you want to know is whether there exists another record for the ContractState, so an exists clause is appropriate.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460278

You can use a NOT EXISTS to select records where there is another record with this TransId but not with this ContractState:

SELECT 
   'tblSQLContractState' as TableName, 
    cs.AccountNbr,  cs.ContractState, cs.TransID,  cs.Product
FROM tblSQLContractState cs
WHERE   cs.TransID IN (80079, 80080)
AND NOT EXISTS                                  -- no other record
(
    SELECT 1 FROM tblSQLContractState cs2
    WHERE cs2.TransID <> cs.TransID             -- with other TransId
      AND cs2.ContractState = cs.ContractState  -- and this  ContractState
)

NOT EXISTS is quite efficient in SQL-Server and can be modified/extended easily. Another advantage is that you can select all columns as opposed to a GROUP BY.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270883

Use window functions. For the data you provided, this should work:

select cs.*
from (select cs.*,
             count(*) over (partition by AccountNbr, ContractState) as cnt
      from tblSQLContractState cs
      where TransID IN (80079, 80080)
     ) cs
where cnt = 1;

Upvotes: 1

Related Questions