Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    9

    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):

    select distinct sb.batchnumber, ur.userid
    from stockbatches sb
    inner join warehouseroles wr on sb.warehouseid = wr.warehouseid
    inner join user_roles ur on wr.roleid = ur.roleid where sb.partid = @part
    order by sb.batchnumber, ur.userid

    - it's a straightforward join between the warehouse and user roles, with a 'distinct' to eliminate any duplications.

    Fair enough, but it's not what I want.

    Using the role data above, with the following batch info:
    Batch 1000, warehouse WAREHOUSE1
    Batch 1001, warehouse WAREHOUSE2
    Batch 1002, warehouse WAREHOUSE3

    I actually want to get this results set:

    Batch Users who can't use this batch
    1000 USER3
    1001 USER1
    1001 USER3

    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.

    I look forward to your suggestions - thanks in advance!

    pmb

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    select ur.*
    from	user_roles ur
    	left outer join warehouseroles wr on ur.roleid = wr.roleid
    	left outer join stockbatches sb
    		on wr.warehouseid = sb.warehouseid
    		and sb.partid = @part
    where	sb.warehouseid is null
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Here's the 'real-world' scenario:
    Where is the DDL from that real world? Now we have to start guessing! I guess I will invent my own data element names and everything. Is this the practice in your shop?

    CREATE TABLE Users
    (user_id CHAR(2) NOT NULL PRIMARY KEY);
    INSERT INTO Users VALUES ('U1'), ('U2'), ('U3')

    CREATE TABLE Warehouses
    (warehouse_name CHAR(2) NOT NULL PRIMARY KEY);
    INSERT INTO Warehouses VALUES ('W1'), ('W2'), ('W3');

    CREATE TABLE Roles
    (role_id CHAR(1) NOT NULL PRIMARY KEY);
    INSERT INTO Roles VALUES ('A'), ('B'), ('C');

    CREATE TABLE User_Roles
    (user_id INTEGER NOT NULL
    REFERENCES Users,
    role_id INTEGER NOT NULL
    REFERENCES Roles,
    PRIMARY KEY (user_id, role_id));

    INSERT INTO User_Roles
    VALUES ('U1', 'A'),
    ('U2', 'A'), ('U2', 'B'), ('U2', 'c'),
    ('U3', 'C') ;

    CREATE TABLE Warehouse_Roles
    (warehouse_name INTEGER NOT NULL
    REFERENCES Warehouses,
    role_id INTEGER NOT NULL
    REFERENCES Roles,
    PRIMARY KEY (warehouse_name, role_id));

    INSERT INTO Warehouse_Roles
    VALUES ('W1', 'A'), ('W1', 'B'),
    ('W2', 'B'),
    ('W3', 'A'), ('W3', 'C'),


    Comparing user and warehouse roles tells the system what warehouses a user can access.
    SELECT UR.user_id, WR.warehouse_name
    FROM Warehouse_Roles AS WR,
    User_Roles AS UR
    WHERE UR.role_id = WR.role_id;

    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:


    CREATE TABLE Batch_Assignments
    (batch_id INTEGER NOT NULL PRIMARY KEY,
    warehouse_name INTEGER NOT NULL
    REFERENCES Warehouses);

    CREATE TABLE Batches
    (batch_id INTEGER NOT NULL
    REFERENCES Batches,
    part_nbr INTEGER NOT NULL,
    PRIMARY KEY (batch_id, part_nbr));

    Short answer: find the users that can get to the part, then do an EXCEPT on the Users table.

    Better answer: get a view with parts and their warehouses dropping out the batches and use it. It seems to be more useful to your queries.

    CREATE VIEW Parts_Warehouses (warehouse_name, part_nbr)
    AS
    SELECT BA.warehouse_name, B.part_nbr
    FROM Batch_Assignments AS BA,
    Batches AS B
    WHERE B.batch_id = AB.batch_id ;

    Did I guess right?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •