Sql-server – Selecting values that are not associated with one side of a many to many relationship

exceptsql server

I have a junction table between categories and values that indicate which categories are associated with which each value. I would like to find the values that are not associated with each category.

Category:

ID CategoryName
-- ------------
 1 category1
 2 category2
 3 category3

CategoryValue:

CategoryID ValueID
---------- -------
         1       1
         1       2
         2       1
         3       2

Value:

ID ValueName
-- ---------
 1 value1
 2 value2
 3 value3

The output I would like for the query is the following:

CategoryID ValueID ValueName
---------- ------- ---------
         1       3 value3
         2       2 value2
         2       3 value3
         3       1 value1
         3       3 value3

I'm pretty stuck on how to approach this as a query. The "junction" table seems to prevent the usual LEFT JOIN WHERE null approach. So any advice would be much appreciated.

Best Answer

You need all combinations of Category and Value (a CROSS JOIN) except those that appear in the CategoryValue. This can be done with a LEFT JOIN / IS NULL

SELECT 
    c.CategoryID, v.ValueID, v.ValueName
FROM 
    Category AS c
  CROSS JOIN
    Value AS v
  LEFT JOIN
    CategoryValue AS cv
      ON  cv.CategoryID = c.CategoryID
      AND cv.ValueID = v.ValueID
WHERE
   cv.CategoryID IS NULL ;

or a NOT EXISTS:

SELECT 
    c.CategoryID, v.ValueID, v.ValueName
FROM 
    Category AS c
  CROSS JOIN
    Value AS v
WHERE
   NOT EXISTS
     ( SELECT *
       FROM CategoryValue AS cv
       WHERE cv.CategoryID = c.CategoryID
         AND cv.ValueID = v.ValueID 
     ) ;

or an EXCEPT solution:

SELECT 
    c.CategoryID, v.ValueID, v.ValueName
FROM 
    Category AS c
  CROSS JOIN
    Value AS v

EXCEPT

SELECT 
    cv.CategoryID, v.ValueID, v.ValueName
FROM 
    CategoryValue AS cv
  JOIN
    Value AS v
      ON  cv.ValueID = v.ValueID ;
Related Question