Selecting values that are not associated with one side of a many to many relationship

Question

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.

Thank you, Andy

Answer 1

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 ;