MySQL Join issues

Edit 2:

Nothing to find here, its my fail in other place.

Who knew != NULL doesn't work in MySQL.

Moderator delete this please?

I got a eav databa开发者_StackOverflowse that I need to select some products with attributes from it.

When I use

select attribute1.value as a1, attribute2.value as a2, products.id
from attributes attribute1, attributes attribute2, products
where product.id = attribute1.product_id and attribute1.name = 'abc' and
    product.id = attribute2.product_id and attribute2.name = 'def'

I don't get all products when an attribute is missing on one products and I need to get all products with NULL if there are missing attributes.

When I use

select attribute1.value as a1, attribute2.value as a2, products.id
from products
left join attributes as attribute1 on (product.id = attribute1.product_id and attribute1.name = 'abc')
left join attributes as attribute2 on (product.id = attribute2.product_id and attribute2.name = 'def')

I get all products, but all products have a1 = NULL even when in the database they dont.

What is the problem?

Example:

products:

 id
1000
1001
1002
1003

attributes:

name    product_id    value
abc        1000         1
abc        1002         2
def        1000         3
def        1001         4

Expected result:

 id     a1      a2
1000    1       3
1001   NULL     4
1002    2      NULL
1003   NULL    NULL

Result from first query:

 id     a1      a2
1000    1       3

Result from second query:

 id     a1      a2
1000   NULL     3
1001   NULL     4
1002   NULL    NULL
1003   NULL    NULL

EDIT:

Fixed the second query and example.

A couple of issues:

1. You're missing a from clause to start the query
2. You need to use the correct table aliases for the attribute.name tests, specifically attribute1.name and attribute2.name.

Try it this way:

select attribute1.value as a1, attribute2.value as a2, products.id
    from products
        left join attributes as attribute1 
            on products.id = attribute1.product_id 
                and attribute1.name = 'abc'
        left join attributes as attribute2 
            on products.id = attribute2.product_id 
                and attribute2.name = 'def'

Query

select attribute1.value as a1, attribute2.value as a2, products.id
left join attributes as attribute1 on (product.id = attribute1.product_id and attribute.name = 'abc')
left join attributes as attribute2 on (product.id = attribute2.product_id and attribute.name = 'def')

Is not right, where table with products?

And also what is the attribute.name, your have no table with name/alias attribute

In order to make it work you have to replace first JOIN with FROM, like this:

SELECT attribute1.value AS a1, attribute2.value AS a2, products.id
FROM attributes AS attribute1 ON (product.id = attribute1.product_id AND attribute.name = 'abc')
LEFT JOIN attributes AS attribute2 ON (product.id = attribute2.product_id AND attribute.name = 'def')