CODEWITHSUNDEEP
sqlmysqlkey-value
David Snabel-Caunt
David Snabel-Caunt

Reputation: 58371

Retrieving a row, with data from key-value pair table in 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'

Upvotes: 2

Views: 5993

Answers (3)

AdaTheDev
AdaTheDev

Reputation: 147304

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

Upvotes: 4

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

The reason rows are only returned is because of the tests in the WHERE clause. Any rows that do not have the correct key1 are ignored altogether - negating your LEFT JOIN.

You could move the key1 tests to your JOIN conditions

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

Upvotes: 2

Jason Musgrove
Jason Musgrove

Reputation: 3583

The the "key" tests in with the LEFT OUTER JOIN predicates, as such:

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

Upvotes: 1

Related Questions