If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Finding a set of data NOT in another set

Hi all,

I'm stuck!

Here's the 'real-world' scenario:

Our products exist in batches. These batches reference a warehouse.
Each warehouse carries a set of 'roles'.
Users on our system also carry 'roles'.
Comparing user and warehouse roles tells the system what warehouses a user can access.

So, if:
WAREHOUSE1 has roles A + B
WAREHOUSE 2 has role B
WAREHOUSE 3 has roles A + C
and
USER1 has role A
USER2 has roles A + B + C
USER3 has role C
then
USER1 will have access to WAREHOUSE1 and WAREHOUSE3
USER2 will have access to all 3 warehouses
USER3 will have access to WAREHOUSE3.

What I need to do is write some SQL which allows a part number to be entered, and a list of batches for that part to be shown with a list of users who *CANNOT* access that part - because they do not have the relevant 'roles' for the warehouse(s) the part is in.

I can do the *opposite* of this quite easily (ie: show users who HAVE got access):

I'm certain I can write the necessary logic programmatically in T-SQL, but wondered if anyone had any suggestions for an elegant, single-statement SQL call? I've seen so many clever solutions to other issues on here... can't help but think I'm missing something.

What I need to do is write some SQL which allows a part number to be entered, and a list of Users for that part to be shown with a list of users who *CANNOT* access that part - because they do not have the relevant 'roles' for the warehouse(s) the part is in.

Parts? Where did you talk about parts? Here is my guess at the degree and cardinality of the relationships: