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.

查看全部
douchibu7040
douchibu7040
2011/01/12 12:24
  • php
  • mysql
  • sql
  • 点赞
  • 收藏
  • 回答
    私信
满意答案
查看全部

2个回复