MySQL Merging Two fields from two tables into one field in result set
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
开发者_StackOverflow中文版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.
SELECT *
FROM (
SELECT itemid, key, iv.value, s.value AS svalue
FROM integervalues iv
JOIN strings s
ON s.id = iv.value
WHERE iv.itemid = 1
UNION ALL
SELECT itemid, key, NULL, value
FROM stringvalues sv
WHERE sv.itemid = 1
) vals
JOIN items i
ON i.id = vals.itemid
Try to use LEFT JOIN:
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
LEFT JOIN AttributeIntegers ai ON i.ItemID=ai.ItemID
WHERE i.ID = 1
And for merging 2 fields into one use CONCAT:
SELECT CONCAT('My field1 data', ', ', 'My field2 data');
results in :
"My field1 data, My field2 data"
精彩评论