MySQL EAV query returns all entries

Question

I have a table that looks like this (except for a lot more entries) and I need to get the boatuid from all entries where taxstatuscode = Paid and taxpaidtoname = NO. But I can't get it to work.

Table:

boatuid    slug              fieldValue
20         taxstatuscode     Paid
20         taxpaidtoname     NO
24         taxstatuscode     Paid
24         taxpaidtoname     SE
25         taxstatuscode     Not Paid
25         taxpaidtoname     N/A

Query:

SELECT a.boatuid
FROM tx_gcnwnxmlparser_boats_specs
LEFT JOIN tx_gcnwnxmlparser_boats_specs a ON (a.slug = "taxstatuscode")
LEFT JOIN tx_gcnwnxmlparser_boats_specs b ON (b.slug = "taxpaidtoname")
WHERE a.boatuid IN(20,24,25)
AND a.fieldValue = "Paid" 
AND b.fieldValue = "NO"
GROUP BY a.boatuid

As it is now it will return all the boatuid's that is in the IN() when it should just return 20. I'm quite new to EAV and joins, so what have I done wrong?

Answer 1

One of the keys when working with EAV is to join using the "entity ID" so that the attributes you select belong to the same entity.

SELECT a.boatuid
    FROM tx_gcnwnxmlparser_boats_specs a
        INNER JOIN tx_gcnwnxmlparser_boats_specs b 
            ON a.boatuid = b.boatuid /* "Entity ID" join condition */
                AND b.slug = 'taxpaidtoname'
    WHERE a.boatuid IN (20,24,25)
        AND a.slug = 'taxstatuscode'
        AND a.fieldValue = 'Paid'
        AND b.fieldValue = 'NO';