sql - What kind of query do I need? -

i have table called physical_exam_tool_and_body_parts has:

id physical_exam_tool_id body_part_id 

the body_parts table has: name_tid

the physical_exam_tools table has: name_tid

the translation table looks like:

id lang text 

i'm trying query:

select physical_exam_tool_text,        body_part_text   physical_exam_tool_and_body_parts  translation.lang = 'fr'  

i want names of body part , physical exam tool lang 'fr'. how can this. i'm new joins.

body_parts , physical_exam_tools tables have:

id name_tid 

name_tid id in translation table. translation table has id lang text. primary key translations composite key (id,lang).

in physical_exam_body_part_and_tool id's in ids (foreign keys) body_parts , physical_exam_tools table.

join translation table twice lang = 'fr'.

select t.[text] [examtool], t2.[text] [bodypart]    physical_exam_tool_and_body_parts p     inner join physical_exam_tool pet     on p.physical_exam_tool_id = pet.physical_exam_tool_id     , t.lang = 'fr'         inner join translation t         on pet.name_tid = t.id     inner join body_parts b     on p.body_part_id = b.body_part_id         inner join translation t2         on b.name_tid = t2.id         , t2.lang = 'fr'