Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 715
Nix Avatar asked Oct 12 '10 19:10

Nix


People also ask

How do you check if a row EXISTS in SQL?

The SQL EXISTS Operator The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.

How do you check if a value EXISTS in a table in SQL?

To test whether a row exists in a MySQL table or not, use exists condition. The exists condition can be used with subquery. It returns true when row exists in the table, otherwise false is returned. True is represented in the form of 1 and false is represented as 0.

What is SELECT @@ rowcount?

Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client.

Does a subquery run for each row?

Each subquery is executed once for every row of the outer query. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement.


1 Answers

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.

like image 126
OMG Ponies Avatar answered Sep 21 '22 23:09

OMG Ponies