Ensure in many-to-many relationship at least one relationship is primary

Sorry for the bad title, if you can think of a better one, let me know.

Many-to-many relationship using tables.

• Product
• ProductCategory
• Category

In the ProductCategory table i have boolean column primarycategory

Each product must 开发者_运维问答have a primary category.

I want to find all products in my database which don't have a primarycategory.

Note: I have assumed field names in tables other than the one you specified.

This should return a distinct list of product IDs that have no primary category. Bit fields in SQL server are numeric, so you can give them to the max() function.

select
   pc.product
from
   ProductCategory pc
group by
   pc.product
having
   max(pc.primarycategory) = 0

The above query assumes that all products have at least one category. If not, try the following:

select
   pc.product
from
   Product p
   left join
   ProductCategory pc on p.id = pc.product
group by
   pc.product
having
   max(isnull(pc.primarycategory, 0)) = 0

Assuming true = value 1, try this:

  Select Product From Product p
  Where Not Exists (Select * From ProductCategory
                    Where Product = p.Product  
                       And primarycategory = 1 )

but if you have control over this database, Move the PrimaryCategory column to the Products table, (and populate it with the category identifier itself, not a boolean), that is where this belongs in a properly normalized schema...