Sql query get all categories and if are active

i have a Relation from N to N from categories to Items in my database.

I have three tables for this:

Categories, Items and Categories_Items

I would like to get all the categories and an extra field beside each one 开发者_开发技巧telling me if is active or not.

This is what i have by the moment:

SELECT    C.ID
          ,C.Name
          ,IsActive=????
      FROM Categories C
      left join Items_Categories IC on IC.CategoryID=C.ID
  where IC.ItemId=@ItemID

The result i would like to get is a list of all the available categories and marked as active those that are in the table Items_Categories and matches with the @ItemId:

ID  Name      IsActive
-- ---------  --------
1  Category1  0
2  Category2  1
3  Category3  .....

Try this:

SELECT  C.ID
                ,C.Name
                ,CASE 
                    WHEN IC.CategoryID IS NULL THEN 0
                    ELSE 1
                END IsActive
    FROM    Categories C LEFT JOIN Items_Categories IC 
        ON IC.CategoryID=C.ID
    AND IC.ItemId=@ItemID

SELECT C.ID
      ,C.Name
      ,CASE WHEN /*Whatever condition makes it active goes here*/ THEN 1 ELSE 0 END AS IsActive
  FROM Categories C
     LEFT JOIN Items_Categories IC on IC.CategoryID=C.ID
  WHERE IC.ItemId=@ItemID

This should help... If you define the condition that makes it active, I'll update the query

SELECT    C.ID
          ,C.Name
          ,isnull((select 1 from [tablename] where [tablename.Category] = C.Name), 0) as IsActive
      FROM Categories C
      left join Items_Categories IC on IC.CategoryID=C.ID
  where IC.ItemId=@ItemID