mysql 键值对_从MySQL中的键值对表中检索数据

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 has the following 3 columns:

customerID

key1

value1

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

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

解决方案

Try this:

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

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