0

I have a list of customer IDs in table newsletter_subscriber. I have an EAV table that contains a lot of information about these customers, table customer_entity_varchar.

For every entry in newsletter_subscriber, I'd like get a specific value from table customer_entity_varchar where the customer ID (named entity_id in this table) is the same as the customer_id in newsletter_subscriber, and the attribute ID is 1.

Here's what I've tried

SELECT newsletter_subscriber.customer_id, customer_entity_varchar.value
FROM newsletter_subscriber
JOIN customer_entity_varchar
    ON newsletter_subscriber.customer_id = customer_entity_varchar.entity_id
    AND customer_entity_varchar.attribute_id = 1

This does not work, and customer_entity_varchar.value contains a wrong value, one that does not have an attribute ID of 1.

1

You can try both of this:

SELECT a.customer_id, b.value, b.attribute_id
FROM a
JOIN b
    ON a.customer_id = b.customer_id
WHERE b.attribute_id = 2

or:

SELECT a.customer_id, b.value, b.attribute_id
FROM a
JOIN (SELECT value, attribute_id FROM b WHERE attribute_id = 2) as b
    ON a.customer_id = b.customer_id
  • Both still return the same wrong value for "b.value" :( – Louis B. Jun 18 '15 at 14:14
  • OK so - give us "SHOW CREATE TABLE a\G (and for b) and give us some sample data (INSERT INTO a VALUES(.....)) (and for b). Give us the result you want and the logic you used to derive it. – Vérace Jun 18 '15 at 14:17
  • Mh, without your data we won't be able to give you a correct example. I've tried it with my own demo data and I get the correct result. Maybe we misunderstand you. – Ionic Jun 18 '15 at 14:20
  • I've updated the original question with my real data. – Louis B. Jun 18 '15 at 14:38
  • Actually, upon recreating the issue in another table with some sample data, I do get the expected result. It just doesn't work in my live data table. :/ – Louis B. Jun 18 '15 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.