invalid records using inner join from two tables

I have this sql statement generated from codeigniter

SELECT * FROM (`products`) LEFT JOIN `products_attributes` ON `products_attributes`.`product_id` = `products`.`id` WHERE `products`.`category` = '1' AND `products`.`sub_category` = '1' AND `products_attributes`.`attribute_id` = '3' AND `products_attributes`.`attribute_id` = '4' AND `products_attributes`.`attribute_id` = '5' GROUP BY `products`.`id`

and I have these tables

products : enter image description here products_attributes :

enter image description here

I need to get the products with the all attributes only, so I will get the product id = 1 if the attributes are 3,4,5 and if the attributes are 3,4,5,6 I will not get product id = 1

Solution

Your sql query is totally wrong. What you did is: Give me the record the attribute_id = 3 AND attribute_id = 4 AND attribute_id = 5 AND attribute_id = 6. One row can not be equal to 3 and 4 at the same time. What you need can be achieved in many different ways. For example:

SELECT
    products.id
FROM  products

    JOIN products_attributes ON products_attributes.product_id = products.id
WHERE
    products.category = '1'
    AND `products_attributes`.`attribute_id` IN (3,4,5,6)


GROUP BY
    products.id
HAVING COUNT(DISTINCT `products_attributes`.`attribute_id`) = 4

If you specify just IN ... then it will take all the records who has at least 1 such attribute_id