Mysql – Problem using multiple JOINs

join;MySQL

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.

Best Answer

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
Related Question