Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
There is a typo in the first line: 1, 3, value3 (not value1) –  ypercube Nov 16 '12 at 10:54
    
Thanks. Fixed it. –  andyb Nov 16 '12 at 11:06
add comment

1 Answer

up vote 3 down vote accepted

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 ;
share|improve this answer
    
Fantastic. Thank you very much. –  andyb Nov 16 '12 at 11:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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