sql - Retrieving a row, with data from key-value pair table in MySQL

Question

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'

Answer 1

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