I have a table T1
| Col1 | Col2 | Col3 |
|---|---|---|
| 1 | A | A1 |
| 2 | A | A2 |
| 3 | A | A3 |
| 4 | A | A4 |
| 6 | B | B1 |
| 7 | B | B2 |
| 8 | B | B3 |
I have another table T2 that looks like this:
| TC1 | TC2 |
|---|---|
| 1 | A1 |
| 2 | A6 |
| 3 | A7 |
| 4 | A8 |
| 6 | B7 |
| 7 | B9 |
| 8 | B0 |
I want to identify if Col3 in T1 having any of the values matching TC2 of T2, then create a new column (Col4) to flag all the Col2 containing any Col3 that are the same to be "Match" if not, "No Match". See example desired output below. I think I might need to use sub-query but not sure how.
Because A1 exists in both T1 and T2, then all rows in Col2 of the same cluster (A) needs to have Col4 flagged with "Match"
| Col1 | Col2 | Col3 | Col4 |
|---|---|---|---|
| 1 | A | A1 | Match |
| 2 | A | A2 | Match |
| 3 | A | A3 | Match |
| 4 | A | A4 | Match |
| 6 | B | B1 | No Match |
| 7 | B | B2 | No Match |
| 8 | B | B3 | No Match |
CodePudding user response：
Seems like you could use a LEFT JOIN and a conditional windowed aggregate:
SELECT T1.Col1,
T1.Col2,
T1.Col3,
MIN(CASE T1.Col3 WHEN T2.Col3 THEN 'Match' ELSE 'No Match' END) OVER (PARTITION BY T1.Col2) AS Col4
FROM dbo.Table1 T1
LEFT JOIN dbo.Table2 T2 ON T1.Col3 = T2.TC2; --I really hope these aren't your free column/object names