1

I'm making a database that handles dialogue translation for projects.

I have a project table, a dialogue table, and a translation table.

Each entry in each table has an id column.

  • dialogue has a projectID column that indicates the project the dialogue is a part of.
  • translation has a dialogueID column that indicates the dialogue entry being translated.

So to help explain this problem, here's a pseudo-query I've constructed:

SELECT * 
FROM translation 
WHERE (translation.dialogueID refers to dialogue where dialogue.projectID = X)

Basically, I want to fetch all translation entries for project X. Since there's no direct route from project -> translation, I'd have to go through project -> dialogue, then dialogue -> translation. I've opted to just add a projectID to the translation table in the meantime, but it is a little clunky.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
milkmanjack
  • 77
  • 1
  • 4

1 Answers1

3

If you know what the id is of the project (X) then you could use:

SELECT t.* FROM translation AS t, dialogue AS d 
WHERE t.dialogueID = d.id AND d.projectID = X

If you don't know the project id but you know it's name (X) or something that you can refer to it, you can use:

SELECT t.* FROM translation AS t, dialogue AS d, project AS p 
WHERE t.dialogueID = d.id AND d.projectID = p.id AND p.name='X'
mongjong
  • 479
  • 3
  • 6
  • I see. So it's actually iterating through every unique pairing of a translation and dialogue row in the first query? That's pretty crazy. I had no idea you could do something like this. Thanks a lot for the quick response. Works perfectly. – milkmanjack Jul 11 '15 at 05:24
  • 2
    Glad I could help. I would recommend indexing your id columns if you haven't already to speed up the select process if you're iterating heaps of rows. There's a good discussion here http://stackoverflow.com/questions/5696897/what-are-the-biggest-benefits-of-using-indexes-in-mysql – mongjong Jul 11 '15 at 05:28
  • 1
    I would recommend using JOIN. It's been around for 20 years, so let's start using it! – Strawberry Jul 11 '15 at 07:55