How to select a record from a many to many table where one id exists in one but not another?

Question

Sorry if that title didn't explain it well.

Here is the table...

I want to select product_id where it is in either 5 or 6 but not if it is in 7.

Maybe I've lost the plot, but I came up with this query

SELECT `product_id` , `category_id`
FROM `wp_wpsc_item_category_assoc`
WHERE (
`category_id` =5
OR `category_id` =6
)
AND `category_id` !=7
LIMIT 0 , 30

Except obviously because of the many to many relationship this will still return a record where category_id is 7, in this case the product with a product_id of 12.

How can I change this query to get all products with either a category_id of 5 or 6, but not if it is also a part of 7.

Thanks guys.

Update

Thanks for all your answers. My daily vote limit is reached, so I'll come back tomorrow and vote up the useful answers.

Answer 1

To cut off the records with category_id = 7 yous should to check all the records for each product_id. So you need a subquery to use:

SELECT 
    product_id, 
    category_id
FROM wp_wpsc_item_category_assoc AS a
WHERE 
(  category_id = 5
OR category_id = 6)
AND
NOT EXISTS (
        SELECT * 
            FROM wp_wpsc_item_category_assoc AS b 
        WHERE a.product_id = b.product_id 
            AND b.category_id = 7
    )

Answer 2

This works on SQL Server and (should be) portable to MySQL.

The gist of the solution is to exclude all products that are in any way linked to category 7.

SELECT  Product_ID
        , Category_ID
FROM    wp_wpsc_item_category_assoc a
        LEFT OUTER JOIN (
          SELECT  Product_ID
          FROM    wp_wpsc_item_category_assoc
          WHERE   Category_ID = 7
        ) anot ON anot.Product_ID = a.ProductID
WHERE   anot.Product_ID IS NULL
        AND a.Category_ID IN (5, 6)        

Answer 3

SELECT product_id, category_id
FROM   wp_wpsc_item_category_assoc WINC
WHERE  WINC.category_id IN (5, 6)
AND    NOT EXISTS
    (SELECT 0 FROM wp_wpsc_item_category_assoc WEXC
     WHERE WEXC.product_id = WINC.product_id
     AND WEXC.category_id IN (7))

We use a subquery to do the exclusion for each particular product_id.

Answer 4

I'm not sure I fully understand the question but maybe you need to use a union or a sub query.

The first think I suggest would be something like:

SELECT product_id , category_id
FROM
 (SELECT product_id , category_id
 FROM wp_wpsc_item_category_assoc
 WHERE product_id !=7)
WHERE (
product_id =5
OR product_id =6
)

I can't test this right now so the brackets might be out slightly but it might do what you're looking for.

Your question says you're interested in the product id, but the sample you gave examined the category, switch if necessary...