CODEWITHSUNDEEP
sqloracle-database
Santi
Santi

Reputation: 451

Query columns in SQL where the columns we want are stored in another table

I am trying to create a query where the selected rows are chosen based on data located on another table. Essentially, one table has the column name that we are trying to match alongside the value we're trying to get.

For example, say these are my tables:

TABLE_1

TABLE_2_COL | VALUE
--------------------
COL_1       | dog
COL_2       | cat
COL_3       | fish

TABLE_2

id |  COL_1 | COL_2 | COL_3
------------------------
1  |  cow   | seal  | snake
2  |  cow   | cat   | snake
3  |  cow   | seal  | dog  
4  |  fish  | seal  | snake

I want to do something like

select t2.* 
from TABLE_1 t1 
left join TABLE_2 t2
on t2[t1.TABLE_2_COL] = t1.VALUE   <- this line obviously not correct, but it's the line that respresents what I'm trying to do

which would return

id |  COL_1 | COL_2 | COL_3
----------------------------
2  |  cow   | cat   | snake

The 'TABLE_2_COL' column of table one will always contain the name of one of the TABLE_2 columns. Since the only row in TABLE_2 that satisfies the query I want to make is 2 (where COL_2 is 'cat'), that is the row that returns.

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is not a great data format, but you can do:

select t2.*
from table_2 t2 join
     table_1 t1
     on (t1.TABLE_2_COL = 'col1' and t1.value = t2.col1) or
        (t1.TABLE_2_COL = 'col2' and t1.value = t2.col2) or
        (t1.TABLE_2_COL = 'col3' and t1.value = t2.col3);

Upvotes: 1

Related Questions