从MySQL中的键值对表中检索数据 [英] Retrieving a row, with data from key-value pair table in MySQL

问题描述

我有两个表，一个叫customer，一个叫customer_attributes.

I have two tables, one called customer and one called customer_attributes.

这个想法是，客户表包含核心客户数据，并且可以根据应用程序的用途对应用程序进行自定义以支持其他属性.

The idea is that the customer table holds core customer data, and the application can be customised to support additional attributes depending on how it is used.

customer_attributes包含以下3列:

customerID
key1
value1

我是否可以检索具有所有其他属性的完整行(如果已指定)，如果没有，则默认为NULL?我正在使用以下查询，但只有两个属性都存在于customer_attributes表中时，它才有效.

Can I retrieve the full row, with any additional attributes if specified, defaulting to NULL if not? I'm using the following query but it only works if both attributes exist in the customer_attributes table.

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"和"test"

In this case the two attributes I'm interested in are called 'wedding_date' and 'test'

推荐答案

尝试一下:

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') 

将ca1/ca2上的2个WHERE条件改为JOIN条件应该对它进行排序

Moving the 2 WHERE conditions on ca1/ca2 into the JOIN condition instead should sort it

这篇关于从MySQL中的键值对表中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！