How to join two tables in a query where the name of the table to be joined is a value in the first table

I have two queries that join 4 tables together. The fourth table is a value that is read from the first tables. Up until now I have been reading that value into a variable and then using the variables in a second query, but I’m wondering if it’s possible to combine this into one query.

This is my query ($node_type_name is passed in from the calling function):

$this->read_db->select('id, head_node_id, data_table_name');
    $this->read_db->from('node_type');
    $this->read_db->where('name', $node_type_name);
    $Q = $this->read_db->get();
    $table_name = $Q->row_array()['data_table_name'];
    $field_name = $table_name . '.node_id';
    $this->read_db->select('node.id, node.name, node.is_head_node, node.node_type_id, node_link.parent_node_id, ' . $table_name . '.id, ' . $table_name . '.node_id');
    $this->read_db->from('node');
    $this->read_db->join('node_link', 'node_link.child_node_id = node.id');
    $this->read_db->join($table_name, $field_name . ' = node.id');
    $M = $this->read_db->get();

The function is a generic function so that someone could send in locations to $node_type_name or organizations and the query would join with the table associated with those names. Right now there are only two “types”, but we may add more in the future and so the function needs to be generic.

Answer

In the end, I created a separate function that gets the first query in an object, which is then passed into the second function.