CODEWITHSUNDEEP
mysql
dmikester1
dmikester1

Reputation: 1392

MySQL select items that do not have a specific category

I have 2 tables: cart_item and cart_item_category.

Both tables have an itemref that is used to join them.

Now, there can be multiple of one itemref in the cart_item_category table because each item can belong to more than one category.

The second field in the cart_item_category table is catid. Now I want to select all items in cart_item that do not have an associated catid of 63.

I cannot figure out how to write that query.

I also only want to show food items.

So here is the start of my query.

I know it is not right because I am simply returning all records not equal to 63.

I only want to show records that do not have an associated catid of 63.

SELECT ci.pid, ci.itemref, ci.name, cic.catid 
FROM cart_item ci 
JOIN cart_item_category cic USING (itemref) 
WHERE ci.isfood = 1 AND cic.catid != 63;

Upvotes: 1

Views: 414

Answers (1)

Egl
Egl

Reputation: 774

It would be:

SELECT ci.pid, ci.itemref, ci.name 
FROM cart_item ci 
WHERE NOT EXISTS (select FROM cart_item_category cic 
    WHERE cic.itemref = ci.itemref 
    AND cic.catid = 63);

Upvotes: 1

Related Questions