Thank you for all the help I have received here and yet, here I'm with a complementary question.
Setup
I have 3 relevant tabels in MySQL:
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
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)
The INTEGERVALUES table uses the Value
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.
Desired result
The SQL query I have now looks like this:
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
This gives me the relevant result
ItemID Key Value sValue sValue
1 Color 2 Red My note book is electronica
1 Type 4 Phone My note book is electronica
What I want is this result:
ItemID Key Value sValue
1 Color 2 Red
1 Type 4 Phone
1 Note Null My note book is electronica
I'm trying to read up on UNION but I don't know if it is the right answer...
Thanks.