BALA s BALA s - 1 year ago 98
SQL Question

Select relation between two columns in SQL


I want select "Data3" on "Column2" relation values.

(Column2)"Data3" relation with (Column1)Data2_005

(Column1)"Data2_005" contains (Column2)Data2

(Column1)"Data1_001" contains (Column2)Data1

If "column1" does not contain with "column2" end the loop.

And here is my Output.
enter image description here

Answer Source

You seem to want to look recursively in the table for Column2 values that are contained in the previous Column1 value. You can do this using a common table expression;

WITH cte(Column1, Column2) AS (
  SELECT Column1, Column2 FROM test t WHERE Column2='Data3'
  SELECT t.Column1, t.Column2 FROM test t 
  JOIN cte 
    ON CHARINDEX(t.column2, cte.Column1) > 0


Column1     Column2
Data2_005   Data3
Data1_001   Data2
GML1        Data1

The first select finds the base case (the row with Column2 equal to Data3).

The second select joins with cte itself which in this case contains the previous row so you can match with the previous Column1 value to find the next row.

Beware of loops (for example a row with Data3_001 + Data3 would fail the query)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download