CODEWITHSUNDEEP
sqlsql-server
user183101
user183101

Reputation: 171

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?

Upvotes: 3

Views: 3992

Answers (3)

EzLo
EzLo

Reputation: 14189

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

Upvotes: 1

Alex Yu
Alex Yu

Reputation: 3547

EXISTS (semi-join) is even more suitable:

SELECT t1.* FROM table1 t1
WHERE EXISTS(
    SELECT * FROM table2 t2
        WHERE 
          t1.table_name=t2.table_name and t1.col_name=t2.col_name 
    )

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use join

select t1.* from table1 t1
join table2 t2
on t1.table_name=t2.table_name 
where t1.index_name in ('Index_1','Index_3') --- provide filter values

Upvotes: 0

Related Questions