php - Complex (or very, very simple) MySQL query? -

i'm making database handles dialogue translation projects.

i have project table, dialogue table, , translation table.

each entry in each table has id column.

• dialogue has projectid column indicates project dialogue part of.
• translation has dialogueid column indicates dialogue entry being translated.

so explain problem, here's pseudo-query i've constructed:

select *  translation  (translation.dialogueid refers dialogue dialogue.projectid = x) 

basically, want fetch translation entries project x. since there's no direct route project -> translation, i'd have go through project -> dialogue, dialogue -> translation. i've opted add projectid translation table in meantime, little clunky.

if know id of project (x) use:

select t.* translation t, dialogue d  t.dialogueid = d.id , d.projectid = x 

if don't know project id know it's name (x) or can refer it, can use:

select t.* translation t, dialogue d, project p  t.dialogueid = d.id , d.projectid = p.id , p.name='x'