Filtering an EAV table (Relational Model)

[Bon]

Hi, all
I have a task to get a list of the items which do not have a certain attribute in the EAV (Entity –Attribute – Value) table which is a part of a relational model. Just in case here is more information about EAV concept .




In my case we have a table with this structure


      
ITEM   ATTRIBUTE   VALUE
Item 1   Attribute 1   Null
Item 1   Attribute 2   Null
Item 1   Attribute 3   500
Item 1   Attribute 4   Null
Item 1   Attribute 5   Null
Item 2   Attribute 1   Null
Item 2   Attribute 2   Null
Item 2   Attribute 3   Null
Item 2   Attribute 4   Null
Item 3   Attribute 6   Null
Item 3   Attribute 7   Null
Item 3   Attribute 8   Null
Item 3   Attribute 2   Null
Item 3   Attribute 1   1000
Item 3   Attribute 29   Null
Item 4   Attribute 15   Null


My task is to get a list of items which DO NOT have an attribute 4 (i.e Item 3 and 4) with help of a filter.

This filter is initially supposed to work with Query Studio where is the filter which shows all the items with this attribute (items 1 and 2 only).  However, when I change settings of the filter  "Show Items with Attribute 4" from "show" to "not show"  -- I get list of all the items, including items 1 and 2. Because they have other records with other attributes and therefore are included in the list

As I said it is not what I want – I want to see only items which do not have attribute 4 at all.

I am trying to implement filters on the model level in CFM. Use of Reports Studio is not a solution as users want to have possibility for ad hoc queries in Query Studio.

While I am trying to fix it on my side maybe anyone from community has already worked with EAV tables, experienced such issues and found a solution.  If yes, please, share it here – I and others I believe would be really grateful.