Search code examples
sql-serverjoinand-operator

SQL Server JOIN with many AND operators does not return data. Why?

Hi I don't understand this simple SQL query with JOIN. I want to select product with two attributes. Here are tables (product and then attributes):

Product table

Attribute table

And here goes two queries

First query do only one join with 4 AND operators and return no data (but it should).

Second query do two join with the tables product <-> attrv_1 and product <-> attrv_2 and works very well. :

Here's the first query

SELECT * 
FROM tblProducts p 
INNER JOIN tblAttributesValues attrv ON p.productid = attrv.productid
                    AND attrv.atrid = 1
                    AND attrv.atrvalue like '%JANICKA IWONA%' 
                    AND attrv.atrid = 2
                    AND attrv.atrvalue like '%N.ERA%'

and second query which return proper data:

SELECT p.* 
FROM tblProducts p 
INNER JOIN tblAttributesValues attrv_1 ON p.productid = attrv_1.productid
                    AND attrv_1.atrid = 1
                    AND attrv_1.atrvalue LIKE '%JANICKA IWONA%'
INNER JOIN tblAttributesValues attrv_2 ON p.ProductID = attrv_2.ProductId
                    AND attrv_2.atrid = 2
                    AND attrv_2.atrvalue LIKE '%N.ERA%'

In the second SQL query I did twice join to find product with two attributes.

Why first query doesn't apply AND operator with one join?

Here's output:

Second query output

Solution

  • "Why first query doesn't apply AND operator with one join?"

    Because there aren't any rows in attributes table which could have atrid = 1 and atrid = 2 at the same time.

    I could thought of a solution which could use group by, having and count clauses to achieve desired results, but your second query would be most probably faster and simpler to understand.