Geek Answers Handbook

SQL Query - ensure there is a row for each value in ()

Currently struggling to find a way to check two tables (effectively many rows for table A)

I have two tables

Table a

 ID 
 A
 B 
 C

Table mapped

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 that for each value in table A there is a row for the value set variable (1, 2,9)

The above example is incorrect, because t must have, for each record in the corresponding record in the table, mapped for each value (1,2,9). Final goal:

Table mapped

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

I know this is confusing, but in general, for every X in (some set) there should be a corresponding entry in the table mapped. I obviously simplified the situation.

, , .

+5
3

:

  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

DISTINCT COUNT , (IE: A, TABLE_B "2" ), . , number , .

HAVING COUNT(...) , IN.

+10

, . , 1, 2 9 , .

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

0

. .

TableA TableMatched, , , TableMatched A. TableMatchedDomain.

TableMatched , , :

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

, . TableMatched , , OtherValues ​​ null. TableMatched , , . TableMatchedDomain, . TableMatchedDomain, . , , .

The reason I formulated this was because I felt that setting invariance in the row configuration in TableMatched was too fragile and, even worse, leading to redundancy. So I removed the restriction from the row groups (in TableMatched) and instead made the entire contents of another table (TableMatchedDomain) determining the correct data form.

0
source

More articles:


All Articles