I have a scenario where I am querying "match clusters" for master records from an MDM database. Each "cluster" has a specific number and within that cluster, you'll have a row/record for the "master" and all associated child records.

We operate in different regions of the world and have a column called 'domain' with reference values ranging from '1001' to 1010' that represent a different region/domain. What I've been able to successfully do to this point is say, "I want the master record from all match clusters where the domain '1008' is in the cluster. That looks like as follows (the "business partner type" is strictly calling out 1001 = customer and 1002 = supplier) - this query just grabs any clusters that has '1008', but doesn't exclude other domain values from being in there:

select * from data.vTest 
where [Match Cluster] in (select distinct [Match Cluster] from data.vTest 
where [Domain] = 1008 and [Match Cluster] is not null and [Business Partner Type] = 1001)
and [Business Partner Type] is null;

The above grabs me any match cluster where 1008 exists (just an example - same concept for 1001, 1002, etc., etc.). What I'm now looking for is, grab me all clusters where *only* one domain value exists. Examples follow:

[![match cluster where only one domain code exists][1]][1]



Then you have the following, which is what I *don't* want to appear - any match clusters that have more than 1 domain value from those "child records":

[![match clusters where multiple domain values exist][3]][3]


What I've tried to this point hasn't worked - the closest I got was the following, which still returns me those "masters" with child records containing more than one domain value:

select * from data.vTest 
where [Match Cluster] in (select [Match Cluster] from data.vTest
where [Domain] not in (1001,1002,1003,1004,1005,1006,1007,1009,1010) and [Match Cluster] is not null and [Business Partner Type] = 1001)
and [Business Partner Type] is null;

Any ideas on the best way to write this query? Hopefully the pics help in stepping back to the big picture of what I'm trying to accomplish. Note that I'm taking a snapshot of one example but the idea is I want to look in ALL match clusters in that column and return all results I'm looking for. Thanks!


  [1]: https://i.sstatic.net/ip04r.png
  [2]: https://i.sstatic.net/u0jfs.png
  [3]: https://i.sstatic.net/eT9LX.png