2

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.

5

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.