SQL Query - Ensure a row exists for each value in ()

Question

Currently struggling with finding a way to validate 2 tables (efficiently lots of rows for Table A)

I have two tables

Table A

 ID 
 A
 B 
 C

Table matched

ID Number
A   1
A   2
A   9
B   1
B   9
C   2

I am trying to write a SQL Server query that basically checks to make sure for every value in Table A there exists a row for a variable set of values ( 1, 2,9)

The example above is incorrect because t should have for every record in A a corresponding record in Table matched for each value (1,2,9). The end goal is:

Table matched

ID Number
A   1
A   2
A   9
B   1
B   2
B   9
C   1
C   2
C   9

I know its confusing, but in general for every X in ( some set ) there should be a corresponding record in Table matched. I have obviously simplified things.

Please let me know if you all need clarification.

Answer 1

Use:

  SELECT a.id
    FROM TABLE_A a
    JOIN TABLE_B b ON b.id = a.id
   WHERE b.number IN (1, 2, 9)
GROUP BY a.id
  HAVING COUNT(DISTINCT b.number) = 3

The DISTINCT in the COUNT ensures that duplicates (IE: A having two records in TABLE_B with the value "2") from being falsely considered a correct record. It can be omitted if the number column either has a unique or primary key constraint on it.

The HAVING COUNT(...) must equal the number of values provided in the IN clause.

Answer 2

Create a temp table of values you want. You can do this dynamically if the values 1, 2 and 9 are in some table you can query from.

Then, SELECT FROM tempTable WHERE NOT IN (SELECT * FROM TableMatched)

Answer 3

I had this situation one time. My solution was as follows.

In addition to TableA and TableMatched, there was a table that defined the rows that should exist in TableMatched for each row in TableA. Let’s call it TableMatchedDomain.

The application then accessed TableMatched through a view that controlled the returned rows, like this:

create view TableMatchedView
select  a.ID,
        d.Number,
        m.OtherValues      
from    TableA a
        join TableMatchedDomain d
        left join TableMatched m on m.ID = a.ID and m.Number = d.Number

This way, the rows returned were always correct. If there were missing rows from TableMatched, then the Numbers were still returned but with OtherValues as null. If there were extra values in TableMatched, then they were not returned at all, as though they didn't exist. By changing the rows in TableMatchedDomain, this behavior could be controlled very easily. If a value were removed TableMatchedDomain, then it would disappear from the view. If it were added back again in the future, then the corresponding OtherValues would appear again as they were before.

The reason I designed it this way was that I felt that establishing an invarient on the row configuration in TableMatched was too brittle and, even worse, introduced redundancy. So I removed the restriction from groups of rows (in TableMatched) and instead made the entire contents of another table (TableMatchedDomain) define the correct form of the data.