0

I have a table P that has a Categories column.

The Categories column is a bitwise flag; I have a Categories Table and if a row from P belongs in 2 different categories(say with Id 1 and 3), its column Category(or IntValue) would be 5, a sum of the first and third bit.

I also have a Exclusion table by Ids in P, for example, I can say that I want to exclude from a search on P with Id=1, every category of Id=2

I would like to select All P, excluding categories in the exclusion table.

For the moment I have this condition :

p.Categories & @SUM_OF_EVERY_CATEGORY_INT_VALUE 
  - (SELECT COALESCE(SUM(IntValue), 0) 
FROM CategoryExceptionByUser 
  LEFT JOIN PCategories 
    ON CategoryExceptionByUser.PCategoryId = PCategories.PCategoryId 
WHERE UserId = @CURRENT_USER) > 0

Which works only if you add every category from a product in the exclusion table. I would like it to work if I add only one of the categories to the exclusion table

I am sorry if it's not clear, english is not my first language. If you need any more detail, please ask away

Here is the link for the example: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1d01677893041ecfd6054e63e5241bad

The query on table P returns Ids 0, 2 and 4, I would like it to return Ids 1, 2 and 3

Paul White
  • 94,921
  • 30
  • 437
  • 687
shin0bi
  • 1
  • 1

1 Answers1

0

For anyone wondering, the solution was :

p.Categories & (SELECT COALESCE(SUM(IntValue), 0) 
FROM CategoryExceptionByUser 
  LEFT JOIN PCategories 
    ON CategoryExceptionByUser.PCategoryId = PCategories.PCategoryId 
WHERE UserId = @CURRENT_USER) <= 0
shin0bi
  • 1
  • 1