检索一排，与在MySQL从键 - 值对的表格数据(Retrieving a row, with da

我有两个表，一个所谓的customer和一个叫customer_attributes 。

我们的想法是，客户表所拥有的核心客户数据，而且应用程序可以进行定制，以支持额外的属性取决于如何使用它。

customer_attributes有以下3列：

customerID
key1
value1

我可以检索整行，与任何附加属性如果指定，如果不是默认为空？ 我使用下面的查询，但如果在customer_attributes表中存在两个属性它才会起作用。

SELECT `customer`.*, `ca1`.`value1` AS `wedding_date`, `ca2`.`value1` AS `test` 
FROM `customer` 
LEFT JOIN `customer_attributes` AS `ca1` ON customer.customerID = ca1.customerID 
LEFT JOIN `customer_attributes` AS `ca2` ON customer.customerID = ca2.customerID 
WHERE (customer.customerID = '58029') 
   AND (ca1.key1 = 'wedding_date') 
   AND (ca2.key1 = 'test')

在这种情况下，这两个属性我感兴趣的被称为“wedding_date”和“测试”

Answer 1:

试试这个：

SELECT `customer`.*, `ca1`.`value1` AS `wedding_date`, `ca2`.`value1` AS `test` 
FROM `customer` 
LEFT JOIN `customer_attributes` AS `ca1` ON customer.customerID = ca1.customerID  AND ca1.key1='wedding_date'
LEFT JOIN `customer_attributes` AS `ca2` ON customer.customerID = ca2.customerID AND ca2.key1='test'
WHERE (customer.customerID = '58029') 

移动2 WHERE上CA1 /钙条件到JOIN条件而应该对其进行排序

Answer 2:

究其原因行才会返回是因为在WHERE子句中的所有测试。 没有正确的key1的任何行被完全忽略 - 否定你的LEFT JOIN。

你可以移动key1的测试，您的加盟条件

SELECT `customer`.*, `ca1`.`value1` AS `wedding_date`, `ca2`.`value1` AS `test` 
FROM `customer` 
LEFT JOIN `customer_attributes` AS `ca1` ON customer.customerID = ca1.customerID AND ca1.key1 = 'wedding_date'
LEFT JOIN `customer_attributes` AS `ca2` ON customer.customerID = ca2.customerID AND ca2.key1 = 'test'
WHERE (customer.customerID = '58029') 

Answer 3:

与左外的“钥匙”测试连接谓词，因为这样的：

SELECT `customer`.*, `ca1`.`value1` AS `wedding_date`, `ca2`.`value1` AS `test` 
FROM `customer` 
LEFT JOIN `customer_attributes` AS `ca1` ON customer.customerID = ca1.customerID 
   AND (ca1.key1 = 'wedding_date') 
LEFT JOIN `customer_attributes` AS `ca2` ON customer.customerID = ca2.customerID 
   AND (ca2.key1 = 'test')
WHERE (customer.customerID = '58029') 

文章来源: Retrieving a row, with data from key-value pair table in MySQL