hydrazi
asked on
Efficient SQL to join many tables?
I am nearly done with a web app in PHP, my first. I have setup a database to keep track of user acknowledgements of reviewing documents (MySQL). I have 4 levels of users: Admins, Managers, Supervisors, and Users.
Admins can do anything. Supervisors can see their own acknowledgement records and those of the department they are in. Users can only see their own acknowledgement records.
My issue is now that they want a "Manager" level that is like a Supervisor because they can see their own records but also the records from MULTIPLE departments. The manager to department relationship is managed in a table called mgrdepts which holds MgrID (same as userid in a table called Users) and DeptID (The ID of the dept from the Departments table).
Currently, a list is displayed like this for Supervisors:
However now, I need to link in a list of not just the user (manager's) department, but also the departments he is linked to in the mgrdepts table.
Help me, Obi Wans. I wants to learn!
Admins can do anything. Supervisors can see their own acknowledgement records and those of the department they are in. Users can only see their own acknowledgement records.
My issue is now that they want a "Manager" level that is like a Supervisor because they can see their own records but also the records from MULTIPLE departments. The manager to department relationship is managed in a table called mgrdepts which holds MgrID (same as userid in a table called Users) and DeptID (The ID of the dept from the Departments table).
Currently, a list is displayed like this for Supervisors:
SELECT * FROM acks, users, files where users.deptid = '$currentdept' and users.manager IS NULL and users.adminuser IS NULL and users.supervisor IS NULL and acks.userid=users.userid and acks.fileid=files.fileid and acks.ackbit IS NULLHowever now, I need to link in a list of not just the user (manager's) department, but also the departments he is linked to in the mgrdepts table.
Help me, Obi Wans. I wants to learn!
ASKER
Something like this?:
USERS TABLES
userid, username, password, supervisor, manager, deptid
1 , jdoe, mypass, 1, 1, 12
ACKS TABLE
ackid, userid, fileid, ackbit, superid
4, 1, 7, 1, 3
DEPARTMENTS TABLE
deptid, deptname
4, Quality
FILES TABLE
fileid, filename, docname, versionid
10, 101.doc, Quality Manual, 1
MGRDEPTS TABLE
relid, mgrid,deptid
3, 1, 4SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow, that one is very interesting! Helping me wrap my head around it....
When I run it with a userid used for testing, I get an error though:
#1146 - Table 'documents.Users' doesn't exist
When I run it with a userid used for testing, I get an error though:
#1146 - Table 'documents.Users' doesn't exist
ASKER
STRIKE THAT..... all my stupid fault. Left a capital letter in the wrong place.
ASKER
Much appreciate the help getting my query working. This was right on the money and only needed minor modifications to make this work! Thanks!
Thanks.