MySQL: selecting joined rows, where children don't belong to a specific parents id

Question

The question might sound a little confusing, I'll do my best to explain it. I have 4 tables:

• store_item: products
• store_cat: product categories. Each product has 1 category.
• store_cat_attribute: category attributes. Each category has N attributes.
• store_item_stock: product stock by attribute

The objective is to get all the ids from the store_item_stock table for a specific product, as long as the store_item_stock.id_attribute is not linked to the current store_item.id_cat

Here's the SQLfiddle for the structure: http://sqlfiddle.com/#!9/d686b9/2

My current query actually gets all the store_item_stock rows for a product, but it is also including the attributes that belong to the current store_item.id_cat:

SELECT store_item_stock.id, store_item_stock.id_attribute, store_item_stock.stock
FROM store_item_stock
LEFT JOIN store_item ON store_item.id_item = store_item_stock.id_item
LEFT JOIN store_cat_attribute ON store_cat_attribute.id_cat = store_item.id_cat
WHERE store_item_stock.id_item = 1 AND store_item.id_cat = 2 GROUP BY store_item_stock.id

For example, id_attribute 2, 3 & 4 belong to id_cat 2, and id_attribute 33 & 34 belong to id_cat 4, so if the query's purpose were to get ALL the store_item_stock rows except for those that have their id_attribute linked to store_item.id_cat 2, it should return:

• id: 104, id_attribute: 33, stock: 26
• id: 105, id_attribute: 34, stock: 28

Answer 1

SELECT store_item_stock.id, store_item_stock.id_attribute, store_item_stock.stock 
FROM store_item_stock 
JOIN store_cat_attribute 
ON store_item_stock.id_attribute=store_cat_attribute.id_attribute
WHERE id_cat NOT IN (
SELECT store_item.id_cat 
FROM store_item JOIN store_cat_attribute 
ON store_item.id_cat=store_cat_attribute.id_cat 
WHERE store_item.id_cat=2 
GROUP BY store_item.id_cat);

I wouldn't think that it's this simple but let's try this one out and see if the condition matches your desired output.