How to implement best matching logic in TSQL (SQL Server 2000)
- by sanjay-kumar1911
I have two tables X and Y:
Table X
C1 C2 C3
1 A 13
2 B 16
3 C 8
Table Y
C1 C2 C3 C4
1 A 2 N
2 A 8 N
3 A 12 N
4 A 5 N
5 B 7 N
6 B 16 N
7 B 9 N
8 B 5 N
9 C 8 N
10 C 2 N
11 C 8 N
12 C 6 N
Records in Table Y can be n number
CREATE TABLE X(C1 INT, C2 CHAR(1), C3 INT);
CREATE TABLE Y(C1 INT, C2 CHAR(1), C3 INT, C4 CHAR(1));
with following data:
INSERT INTO X VALUES (1 'A',13 );
INSERT INTO X VALUES (2 'B',16 );
INSERT INTO X VALUES (3 'C',8 );
INSERT INTO Y VALUES (1,'A', 2,'N');
INSERT INTO Y VALUES (2,'A', 8,'N');
INSERT INTO Y VALUES (3,'A', 12,'N');
INSERT INTO Y VALUES (4,'A', 5,'N');
INSERT INTO Y VALUES (5,'B', 7,'N');
INSERT INTO Y VALUES (6,'B', 16,'N');
INSERT INTO Y VALUES (7,'B', 9,'N');
INSERT INTO Y VALUES (8,'B', 5,'N');
INSERT INTO Y VALUES (9,'C', 8,'N');
INSERT INTO Y VALUES (10,'C', 2,'N');
INSERT INTO Y VALUES (11,'C', 8,'N');
INSERT INTO Y VALUES (12,'C', 6,'N');
EXPECTED RESULT
Table Y
C1 C2 C3 C4
1 A 2 N
2 A 8 Y
3 A 12 N
4 A 5 Y
5 B 7 N
6 B 16 Y
7 B 9 N
8 B 5 N
9 C 8 Y
10 C 2 N
11 C 8 N
12 C 6 N
How do I compare value of column C3 in Table X with all possible matches of column C3 of Table Y and to mark records as matched and unmatched in column C4 of Table Y?
Possible matches for A (i.e. value of column C2 in Table X) would be (where R is row number i.e. value of column C1 in Table Y):
R1,
R2,
R3,
R4,
R1+R2,
R1+R3,
R1+R4,
R2+R3,
R2+R4,
R3+R4,
R4+R5,
R1+R2+R3,
R1+R2+R4,
R2+R3+R4,
R1+R2+R3+R4
