SQL: Help writing select query using JOIN

Question

I have three tables:

Languages

ID    LangName    LangAbbr
1     English     en
2     Spanish     es

Strings

ID    String
1     Hello
2     Goodbye

Translations

ID    LangID    StringID    Translation
1     1         1           Hello
2     2         1           Hola
3     1         2           Goodbye
4     2         2           Adios

As of now, I have had the ID of the language from the languages table, and ran a query to get all of the translations for that language:

SELECT t.Translation, s.String FROM Strings 
AS s JOIN Translations AS t ON s.ID = t.StringID 
AND LangID = :LangID

This works great. However, I needed to update some of my code elsewhere where now I will no longer have access to the LangID, but instead, only the LangAbbr from the Languages table (which up until now was not used in this query).

My question is, how do I update this query to perform the following:

1. Get the ID from the Languages table whose LangAbbr = X (my variable)

2. Using that ID, return an array whose keys equal the names of each String in the Strings table, and whose values equal the Translation in the Translations table for that string/language ID.

Answer 1

You really only need to add another join through Strings to Languages and since you don't have a requirement to return untranslated strings for a given language there's nothing complicated. To return all Spanish strings as a String,Translation pair for exampel, the SQL would be (http://sqlfiddle.com/#!9/30273/4):

SELECT 
  String,
  Translation
FROM
  strings s
  INNER JOIN translations t ON s.ID = t.StringID
  INNER JOIN languages l ON t.LangID = l.ID
-- use PDO param :langAbbr
WHERE LangAbbr = 'es'

Based on your comment I'll assume you are using PHP/PDO. The fetch loop can be structured to set array keys as:

// Assuming a successful prepare()/execute() of the above query
// into $stmt
$stmt = $conn->prepare($sql_from_above);
$stmt->execute(array(':langAbbr' => 'es'));

$translations = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  // Index the $translations array by String during the fetch
  $translations[$row['String']] = $row['Translation'];
}