SELECT product.id, sku, attribute_language.name, content, stock, custitem_maincategory, custitem_subcategory, store_product.active, brand.name
FROM `product` 
JOIN store_product ON product.id = store_product.product_id
JOIN product_stock ON product.id = product_stock.product_id
JOIN attribute ON product.id = attribute.product_id
JOIN attribute_language ON attribute.id = attribute_language.attribute_id
JOIN brand ON brand.id = product.brand_id
WHERE store_product.active = 1
AND product.brand_id = 1
AND store_product.store_id = 1 
GROUP BY product.id