title: SQL left join with multiple rows into one row
body: <p>Basically, I have two tables, Table A contains the actual items that I care to get out, and Table B is used for language translations.</p>
  
  <p>So, for example, Table A contains the actual content. Anytime text is used within the table, instead of storing actual varchar values, ids are stored that relate back to text stored in Table B. This allows me to by adding a languageID column to Table B, have multiple translations for the same row in the database.</p>
  
  <p>Example:</p>
  
  <h2>Table A</h2>
  
  <ul>
  <li>Title (int) </li>
  <li>Description (int)</li>
  <li>Other Data....</li>
  </ul>
  
  <h2>Table B</h2>
  
  <ul>
  <li>TextID (int) - This is the column
  whose value is stored in other tables</li>
  <li>LanguageID (int)</li>
  <li>Text (varchar)</li>
  </ul>
  
  <p>My question is more a call for suggestions on how to best handle this. Ideally I want a query that I can use to select from the table, and get the text as opposed to the ids of the text out of the table. Currently when I have two text items in the table this is what I do:</p>
  
  <pre><code>SELECT C.ID, C.Title, D.Text AS Description
  FROM
  (SELECT A.ID, A.Description, B.Text AS Title
  FROM TableA A, TranslationsTable B
  WHERE A.Title = B.TextID AND B.LanguaugeID = 1) C
  LEFT JOIN TranslationsTable D
  ON C.Description = D.TextID AND D.LanguaugeID = 1
  </code></pre>
  
  <p>This query gives me the row from Table A I am looking for (using where statements in the inner select statement) with the actual text based on the language ID used instead of the text ids.</p>
  
  <p>This works fine when I am only using one or two text items that need to be translated, but adding a third item or more, it starts to get really messy - essentially another left join on top of the example.</p>
  
  <p>Any suggestions on a better query, or at least a good way to handle 3 or more text items in a single row?</p>
  
  <p>Per suggestions, I've added an example of the two tables:</p>
  
  <pre><code>    Table A  
      ---------------------------
      ID    |Title    |Description  
      ---------------------------  
      1     |1        |2  
      ---------------------------  
      2     |3        |4  
      --------------------------- 
  
      Table B (Translations Table) 
      ---------------------------
      ID    |LanguaugeID|Text  
      ---------------------------  
      1     |1        |Here is title one
      ---------------------------  
      1     |2        |Here is a title one in espanol
      ---------------------------
      2     |1        |Here is description one
      ---------------------------  
      2     |2        |Here is description one in espanol
      ---------------------------
      3     |1        |Title 2
      ---------------------------  
      4     |1        |Description 2
      ---------------------------   
  </code></pre>
  
  <p>What I want is to be able to pull a row out of table A that already has the text from table B, not just the ids - and to be able to do this for several columns that need translations.</p>