SELECT
p.id,
CASE
WHEN EXISTS (SELECT 1 FROM b_codes WHERE p.Col1 LIKE value) THEN 'b'
WHEN EXISTS (SELECT 1 FROM s_codes WHERE p.Col1 LIKE value) THEN 'S'
WHEN EXISTS (SELECT 1 FROM u_codes WHERE p.Col1 LIKE value) THEN 'U'
ELSE 'U'
END AS Flag
FROM p;
output:
| ID | Flag |
|---|---|
| AAA | b |
| AAA | S |
| AAA | U |
| AAA | U |
| BBB | U |
| BBB | U |
| BBB | U |
| BBB | U |
| CCC | b |
| CCC | U |
| DDD | U |
| DDD | U |
| DDD | U |