MySQL join with match on relational table

Question

I'm dealing with a MySQL DB schema that has several tables and I was wondering if it's possible to submit 1 query to get all of the data I need. The one hiccup I'm having is there is a table that references a key id to another table. For instance-

Table1:

________________________
|  id   |  profession   |
+---------+-------------+
| 1     |   stockbroker |   
| 2     |   doctor      |
| ...   |   ...         |

Tabel2:

____________________________
|  id        |  rehab      |
+------------+-------------+
| 1          | meth-head   |   
| 2          | alcoholic   |

Tabel3:

__________________________
|  table1_id |  table2_id |
+------------+------------+
|      1     |      2     |   

Is it possible to get the key_id value from Table2 if I only know Table1.key_id?

Something along the lines of -

SELECT table2.value1 FROM Table1, Table2, Table3 WHERE (the corresponding table2_id value of table1_id in Table3)

Any help is greatly appreciated.

Thanks!

Answer 1

You haven't given much information but,

SELECT t2.value1
FROM table2 AS t2, table1 AS t1, table3 AS t3
WHERE t1.key_id = 1 
AND t1.key_id = t3.table1_id AND t3.table2_id = t2.key_id;

would work for that limited example.

http://sqlfiddle.com/#!2/9cf42/5

Answer 2

If I'm reading your question correctly it looks like Table3 is a pivot table, so the tables 1 and 2 have a many-to-many relationship. You would have to join from Table1 to Table3 then back to Table2.

SELECT t2.value1, t2.value2
FROM Table1 t1
    INNER JOIN Table3 t3 ON t1.key_id = t3.table1_id
    INNER JOIN Table2 t2 ON t3.table2_id = t2.key_id
WHERE t1.key_id = [your value]

Keep in mind this could produce many values for Table2 per one value in Table1, and vice-versa.