0

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.

Santi
  • 381
  • 1
  • 3
  • 7

1 Answers1

1

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);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This would work. However, I was hoping to do it in a way that didn't include hard coding every column into the statement – Santi Sep 17 '18 at 20:44
  • @Santi . . . There is no other way to refer to a column in a table -- that I can think of. If you stored `table_2` as key-value pairs, then you could more easily do what you want. – Gordon Linoff Sep 17 '18 at 23:36