MySQL将两个表中的两个字段合并到结果集中的一个字段中
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.
感谢您在此收到的所有帮助,但是,我在这里有一个补充问题。 p>
设置 h2>
我在MySQL中有3个相关的表格: p>
STRINGVALUES
ID ItemID键值\ n1 1注意我的笔记本是electronica
INTEGERVALUES
ID ItemID键值
1 1颜色2
2 1类型4
STRINGS
ID值
1石头
2红色
3蓝色
4手机
代码> pre>
其他字段已被删除,仅显示相关内容。
ItemID是文章的标识符。 在这种情况下,我只输入了第1条的信息(ItemID = 1) p>
INTEGERVALUES表使用 Value code>作为ID来查找STRINGS中的值 table(因此是一个只有整数的单独表)。 字符串表也支持多种语言,而StringValues是用户输入的数据。 p>
所需结果 h2>
我现在看到的SQL查询如下所示 : p>
SELECT i。*,ai.Key,ai.Value,
(SELECT s.Value FROM Strings s WHERE ai.Value = s.StringID)sValue,\ n(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>
这给了我相关的结果 p>
ItemID键值sValue sValue
1颜色2红色我的笔记本是electronica
1 Type 4 Phone 我的笔记本是electronica
code> pre>
我想要的是这个结果: p>
ItemID键值sValue
1颜色 2 Red
1 Type 4 Phone
1 Note Null我的笔记本是electronica
code> pre>
我正在尝试阅读UNION,但我不知道是否 是正确答案...... p>
谢谢 s。 p>
div>
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"