Excluding Group Item Records from an Exclude Groups table
I've trying to display Item records for a particular Client where those Items do not belong to certain Groups using a join table. These are 4 tables:
Items (PK_ItemID, FK_GroupID, ItemName)
Group (PK_GroupID, GroupName)
Client (PK_ClientID, ClientName)
Client_Group_Exclude_Join (FK_ClientID, FK_GroupID)
Item/Group table is self explanatory.
Clients may have zero or more groups associated to them (however this association is to tell us which Groups or Items in that group this Client is NOT to see!) So in other words using a portal we'd say out of all the groups this particular client cannot see items belonging to those groups associated to that Client Table.
I've added all the relationships in particular (Client_Group_Exclude_Join/FK_GroupID != Group/PK_Group)
I've added sample data however if I try to display a Portal in the Client layout which lists Items I don't seem to get the correct Items list.
If I have no Groups associated for a particular Client then no Items appear?!
If I have a group added to Clients then then items NOT belonging to that group appear - which is what I want!! :-)
If I have more then one Groups added to that client then all items for all Groups reappear?!
If I have all the group added to the client then No Item should appear - however All items are appearing?!
Can anyone please tell me what am I doing wrong?
Thanks in advance.