Filter data in one table based on data in another table

I have two tables presented below. First table contains information about indexes. Each index can be applied to one or more columns. Second table contains set of pairs: table name – column name.

I need to somehow get a list of indexes from table #1 applied to columns from table #2. Result should include all columns for filtered indexes (see result table below).

#1
+---------------------------------------+
| Index name | Table name | Column name |
+---------------------------------------+
| Index_1    | Table_A    | Column_A_1  |
| Index_1    | Table_A    | Column_A_2  |
| Index_2    | Table_A    | Column_A_1  |
| Index_2    | Table_A    | Column_A_3  |
| Index_3    | Table_B    | Column_B_1  |
| Index_3    | Table_B    | Column_B_2  |
| Index_4    | Table_C    | Column_C_1  |
+---------------------------------------+

#2
+--------------------------+
| Table name | Column name |
+--------------------------+
| Table_A    | Column_A_2  |
| Table_B    | Column_B_1  |
+--------------------------+

Result:
+---------------------------------------+
| Index name | Table name | Column name |
+---------------------------------------+
| Index_1    | Table_A    | Column_A_1  |
| Index_1    | Table_A    | Column_A_2  |
| Index_3    | Table_B    | Column_B_1  |
| Index_3    | Table_B    | Column_B_2  |
+---------------------------------------+

Can I do that per one “SELECT” operation without using additional table? If so, how?

Answer

A direct JOIN or EXISTS won’t cut it, since you want to display a set (a complete index) if at least one of it’s rows satisfies a condition.

You need to first determine which indexes match against the other table and then display all it’s rows:

;WITH IndexMatches AS
(
    SELECT DISTINCT
        I.IndexName
    FROM
        IndexTable AS I
        INNER JOIN ColumnsTable AS C ON
            C.TableName = I.TableName AND 
            C.ColumnName = I.ColumnName
)
SELECT
    I.*
FROM
    IndexTable AS I
    INNER JOIN IndexMatches AS M ON I.IndexName = M.IndexName
ORDER BY
    I.IndexName,
    I.TableName,
    I.ColumnName

Or with EXISTS:

SELECT
    I.*
FROM
    IndexTable AS I
WHERE
    EXISTS (
        SELECT
            'at least one column match'
        FROM
            IndexTable AS I2
            INNER JOIN ColumnsTable AS C ON
                C.TableName = I2.TableName AND 
                C.ColumnName = I2.ColumnName
        WHERE
            I.IndexName = I2.IndexName)
ORDER BY
    I.IndexName,
    I.TableName,
    I.ColumnName