How do I correctly construct this sql query?

Question

I have five tables:

User

 user_id | name
--------------------
    0    | Mark  
    1    | Jen
    2    | Mbali
    3    | Mbabani
    4    | Fang Zhao

Role

 role_id | name
--------------------
    3    | Employee
    4    | Customer Asia
    5    | Customer Africa

User_Role_Assoc

 role_id | user_id
--------------------
    3    |   0
    3    |   1
    3    |   2
    5    |   3
    4    |   4

Role_Reps

 role_id | user_id
--------------------
    4    |   0
    4    |   1

Request

 req_id  | user_id
--------------------
    8    |   3
    9    |   3
   10    |   4
   11    |   4

Mark, Jen and Mbali are all employees (role_id=3) of a fictional company. The other two users Mbabani and Fang Zhao are customers who create requests.

The query should be able to see that req_id 8 and 9 were requested by a user (Mbabani[3]) who belongs to the Customer Africa role (by way of User_Role_Assoc) which has no assigned representatives (Role_Reps).

The query should be able to see that req_id 10 and 11 were requested by a user(Fang Zhao[4]) who belongs to the Customer Asia role (by way of User_Role_Assoc) which DOES have representatives.

All employees should be able to see all requests. Unless there is a role representative assingned to that role in the Role_Reps table. If there are any representatives, in this case Mark and Jen, they are the ONLY ONES alowed to see the request. If there are NO representatives defined in the Role_Reps table then everyone should be able to see the requests.

So I need a query that:

If I pass in a userid=2 (Mbali) I should get the following results:

 req_id  | user_id
--------------------
   10    |   4
   11    |   4

If I pass in a userid=0 or userid=1 (Mark or Jen) I should get the following results:

 req_id  | user_id
--------------------
    8    |   3
    9    |   3
   10    |   4
   11    |   4

I hope I've made myself clear.

---

UPDATE

Here are the DDLs to generate the tables with the data:

DROP TABLE IF EXISTS t_user;


CREATE TABLE t_user (
    userid          integer PRIMARY KEY,
    name            varchar(20)
);

GRANT ALL PRIVILEGES ON t_user TO PUBLIC;

INSERT INTO t_user (userid, name) VALUES (0,'Mark');
INSERT INTO t_user (userid, name) VALUES (1,'Jen');
INSERT INTO t_user (userid, name) VALUES (2,'Mbali');
INSERT INTO t_user (userid, name) VALUES (3,'Mbabani');
INSERT INTO t_user (userid, name) VALUES (4,'Fang Zhao');



DROP TABLE IF EXISTS t_role;


CREATE TABLE t_role (
    roleid          integer PRIMARY KEY,
    name            varchar(20)
);

GRANT ALL PRIVILEGES ON t_role TO PUBLIC;

INSERT INTO t_role (roleid, name) VALUES (3,'Employee');
INSERT INTO t_role (roleid, name) VALUES (4,'Customer Asia');
INSERT INTO t_role (roleid, name) VALUES (5,'Customer Africa');


DROP TABLE IF EXISTS t_user_role_assoc;


CREATE TABLE t_user_role_assoc (
    roleid          integer,
    userid          integer,
    primary key(roleid, userid)
);

GRANT ALL PRIVILEGES ON t_user_role_assoc TO PUBLIC;

INSERT INTO t_user_role_assoc (roleid, userid) VALUES (3,0);
INSERT INTO t_user_role_assoc (roleid, userid) VALUES (3,1);
INSERT INTO t_user_role_assoc (roleid, userid) VALUES (3,2);
INSERT INTO t_user_role_assoc (roleid, userid) VALUES (5,3);
INSERT INTO t_user_role_assoc (roleid, userid) VALUES (4,4);




DROP TABLE IF EXISTS t_role_reps;


CREATE TABLE t_role_reps (
    roleid          integer,
    userid          integer,
    primary key(roleid, userid)
);

GRANT ALL PRIVILEGES ON t_role_reps TO PUBLIC;

INSERT INTO t_role_reps (roleid, userid) VALUES (4,0);
INSERT INTO t_role_reps (roleid, userid) VALUES (4,1);


DROP TABLE IF EXISTS t_request;


CREATE TABLE t_request (
    req_id          integer PRIMARY KEY,
    userid          integer
);

GRANT ALL PRIVILEGES ON t_request TO PUBLIC;

INSERT INTO t_request (req_id, userid) VALUES (8,3);
INSERT INTO t_request (req_id, userid) VALUES (9,3);
INSERT INTO t_request (req_id, userid) VALUES (10,4);
INSERT INTO t_request (req_id, userid) VALUES (11,4);

Answer 1

Hope I understand it, but this should get you started and close (if I understand if right)

select r.req_id,
u1.name as requestingUser,
rl.name as BelongsToRole,
coalesce(r2.name, '') as RoleRep

from request R 
join user u1 on r.user_id=u1.user_id
join user_role_assoc ura on ura.user_id=u1.user_id
join role rl on rl.role_id=ura.role_id
left join role_Reps r1 on r1.role_id=rl.role_id
left join users R2 on r2.user_id=rl.user_id

Answer 2

Hi I gave it a try on MSSQL.

The First Select-Statement gives you the request of the assigned reps, the second Select Statement the request of users, where no reps are assigned. It looks not optimized I know, but at least it works. I tried it out.

SELECT EMPLOYEE.USER_ID
   ,EMPLOYEE.NAME AS EMPLOYEE_NAME
   ,CUSTOMER.NAME AS ASSIGNED_CUSTOMER_NAME
   ,ASSIGNED_REQUESTS.REQ_ID AS REQ_ID
    FROM USR EMPLOYEE
INNER JOIN USER_ROLE_ASSOC URA ON EMPLOYEE.USER_ID = URA.USER_ID AND URA.ROLE_ID = 3
LEFT JOIN ROLE_REPS RR ON EMPLOYEE.USER_ID = RR.USER_ID
LEFT JOIN USER_ROLE_ASSOC CUSTOMER_ROLE ON RR.ROLE_ID = CUSTOMER_ROLE.ROLE_ID
LEFT JOIN USR CUSTOMER ON CUSTOMER_ROLE.USER_ID = CUSTOMER.USER_ID
LEFT JOIN REQUEST ASSIGNED_REQUESTS ON CUSTOMER.USER_ID = ASSIGNED_REQUESTS.USER_ID
WHERE ASSIGNED_REQUESTS.REQ_ID IS NOT NULL
UNION 
SELECT EMPLOYEE.USER_ID
   ,EMPLOYEE.NAME AS EMPLOYEE_NAME
   ,CUSTOMER.NAME AS ASSIGNED_CUSTOMER_NAME
   ,ASSIGNED_REQUESTS.REQ_ID AS REQ_ID
    FROM USR EMPLOYEE
INNER JOIN USER_ROLE_ASSOC URA ON EMPLOYEE.USER_ID = URA.USER_ID AND URA.ROLE_ID = 3
LEFT JOIN ROLE R ON R.ROLE_ID NOT IN (SELECT ROLE_ID FROM ROLE_REPS) AND R.ROLE_ID <> 3
LEFT JOIN USER_ROLE_ASSOC CUSTOMER_ROLE ON R.ROLE_ID = CUSTOMER_ROLE.ROLE_ID
LEFT JOIN USR CUSTOMER ON CUSTOMER_ROLE.USER_ID = CUSTOMER.USER_ID
LEFT JOIN REQUEST ASSIGNED_REQUESTS ON CUSTOMER.USER_ID = ASSIGNED_REQUESTS.USER_ID
WHERE ASSIGNED_REQUESTS.REQ_ID IS NOT NULL

If you just want the requests of one Employee, just add to the where clause on both select:

And Employee.User_Id = ?

Answer 3

Just as general advice, I find it's often easier to see what's going on by making a new set of relations using natural keys instead of surrogate ones. So in this case, I might do this:

User
-------------------------
 username    | name
-------------------------
  mark       | Mark  
  jen        | Jen
  mbali      | Mbali
  mbabani    | Mbabani
  fangzhao   | Fang Zhao

Role
------------------------------------
 role_id          | name
------------------------------------
  employee        | Employee
  customer_asia   | Customer Asia
  customer_africa | Customer Africa

User_Role_Assoc
------------------------------
 role_id          | user_id
------------------------------
  employee        | mark
  employee        | jen
  employee        | mbali
  customer_africa | mbabani
  customer_asia   | fangzhao

Role_Reps
----------------------------
 role_id          | user_id
----------------------------
  customer_asia   | mark
  customer_africa | jen

Request
---------------------
 req_id  | user_id
---------------------
  8      | mbabani
  9      | mbabani
 10      | fangzhao
 11      | fangzhao

Now when you go to construct your query, you don't have to do all the joins at once, you can interactively add another join and another and it will be much clearer to you as you get closer to what you need. When you're done you can take that query right back to your actual database with surrogate IDs for everything, and it will "just work."

With respect to your actual question, you're counting on the absence of something triggering the presence of something else. I usually find this kind of thing to be difficult to express in SQL. You may find it easier if you add additional data to one of the relations to signify that something is public or global, and do the restricted query UNIONed with a separate query to get all global stuff.

Answer 4

I think this should get what you want

SELECT R.req_id, R.user_id
FROM Request R
    INNER JOIN User_Role_Assoc A ON A.user_id = R.user_id
    LEFT JOIN Role_Reps L ON L.role_id = A.role_id
WHERE L.user_id IS NULL OR L.user_id = @user_id