Joining tables with and without concatenated columns

Question

I have a scenario similar to the following question " join comma delimited data column" . But this question is different that I need to select from table T1.

Reference Fiddle

CREATE TABLE T1  ([col1] varchar(2), [col2] varchar(5));

INSERT INTO T1  ([col1], [col2], [col3])
VALUES
    ('C1', 'john',8),
    ('C2', 'alex',10),
    ('C3', 'piers',10),
    ('C4', 'sara',10);

CREATE TABLE T2  ([col1] varchar(2), [col2] varchar(8));

INSERT INTO T2  ([col1], [col2] , [col3])
VALUES
    ('R1', 'C1,C4',10),
    (NULL, 'C3,C2,C5',10),
    ('R3', 'C5,C1,C4',8);

In the final result, I need to select values from table T1, and a flag whether there is a corresponding non-null value in table T2. Also need to consider [col3] while join.

Expected Result

--- John, Yes --- (Because ‘R3’ is present)

--- Alex, No ---

--- Piers, No ---

--- Sara, Yes --- (Because ‘R1’ is present)

QUESTION

What is the best way to write this query in Oracle 8i?

Answer 1

Try this:

SELECT 
  T1.col2
  ,CASE WHEN T2.col1 IS NULL THEN 'No' ELSE 'Yes' END
FROM T1
  LEFT OUTER JOIN T2 ON ',' + T2.col2 + ',' LIKE '%,' + T1.col1 + ',%' 
                    AND T1.col3 = T2.col3

If you have possible duplicates and you want eliminate them (showing a Yes over a No) then use this:

SELECT DISTINCT
  T1.col2
  ,MAX(CASE WHEN T2.col1 IS NULL THEN 'No' ELSE 'Yes' END)
FROM T1
  LEFT OUTER JOIN T2 ON ',' + T2.col2 + ',' LIKE '%,' + T1.col1 + ',%'
                    AND T1.col3 = T2.col3
GROUP BY
  T1.col2