How to implement best matching logic in TSQL (SQL Server 2000)

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

you stated the following

"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?"

I believe you example was comparing Column 2 in Table X with column in Table y but this will do either just change the column name around.

Note I havent tested this but should head you in the right direction.

Update TableY
Set Column4 = myComparison.myValue
From (Select
       TableY.Column1,
       Case When (TableX.Column1 IS NULL) THEN
          'N'
       ELSE 'Y' END as MyValue
     From TableY
     Left Join TableX ON TableY.Column3 = TableX.Column3) myComparison
Where myComparison.Column1 = Column1