title: MySQL Merging Two fields from two tables into one field in result set
body: <p>Thank you for all the help I have received here and yet, here I'm with a complementary question.</p>
  
  <h2>Setup</h2>
  
  <p>I have 3 relevant tabels in MySQL:</p>
  
  <pre><code>STRINGVALUES
  ID   ItemID   Key    Value
  1    1        Note   My note book is electronica
  
  INTEGERVALUES
  ID   ItemID Key    Value
  1    1      Color  2
  2    1      Type   4
  
  STRINGS
  ID   Value
  1    Stone
  2    Red
  3    Blue
  4    Phone
  </code></pre>
  
  <p>Other fields has been removed to only show what is relevant.
  ItemID is the identifier for an Article. In this case I have only entered information for Article 1 (ItemID=1)</p>
  
  <p>The INTEGERVALUES table uses the <code>Value</code> as an ID to lookup a value in the STRINGS table (hence a separate table with only integers). The strings table also have support for multiple languages while StringValues are data entered by users.</p>
  
  <h2>Desired result</h2>
  
  <p>The SQL query I have now looks like this:</p>
  
  <pre><code>SELECT i.*, ai.Key, ai.Value,
  (SELECT s.Value FROM Strings s WHERE ai.Value=s.StringID) sValue,
  (SELECT ats.Value FROM attributestrings ats WHERE ats.ItemID=i.ItemID) sValue
  FROM items i
  INNER JOIN AttributeIntegers ai ON i.ItemID=ai.ItemID
  WHERE i.ID = 1
  </code></pre>
  
  <p>This gives me the relevant result</p>
  
  <pre><code>ItemID Key    Value  sValue  sValue
  1      Color  2      Red     My note book is electronica
  1      Type   4      Phone   My note book is electronica
  </code></pre>
  
  <p>What I want is this result:</p>
  
  <pre><code>ItemID Key    Value  sValue
  1      Color  2      Red
  1      Type   4      Phone
  1      Note   Null   My note book is electronica
  </code></pre>
  
  <p>I'm trying to read up on UNION but I don't know if it is the right answer...</p>
  
  <p>Thanks.</p>