SQL method to query rows based on parameters and join table

Question

I have the following GET endpoints in my Ruby project:

/endpoint/:special_param

I have a database with 2 tables. Table 1 contains the following columns: id, special_param_column, joinable_column

Table 2 contains the following columns: id, joinable_column, other_data

This is my code to handle the request in the database model:

def self.some_function(special_param)
  data = find_by_sql(["SELECT ..."])

  return data
end

What should I write inside find_by_sql to select rows in table 2 that have the same joinable_column value as in table 1 where the special_param_column value is equal to the one passed to special_param value in function?

For example, assume the table contains the following data:

Table 1
id | special_param_column | joinable_column
===========================================
1    208                    Keanu Reeves
2    349                    Jack Black
...

Table 2
id | other_data | joinable_column
=================================
1    24           Keanu Reeves
2    68           Jack Black
3    11           Jack Black
4    0            Keanu Reeves
...

If special_param = 208, I want to return row 1 and row 4 in table 2

Answer 1

SELECT (* or whatever you want to return) from table1 t1
INNER JOIN table2 t2
ON t2.joinable_column = t1.joinable_column
WHERE t1.special_param_column = special_param