根据列值查询另一个表中的值

I am attempting to create a query that pulls information from two other tables, however I only know which table to pull from based on a column in another table. I'm currently looking into doing this using a stored procedure (e.g. build the query and then run it) but I wanted to know if there is a better way to do this, or if I could accomplish it in a single query.I am attempting to create a query that pulls in