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:

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 NULL

Open in new window


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!
hydraziAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
Can you provide some sample data from each of the tables that is used for the relationships between users, departments, managers, etc?
Thanks.
0
hydraziAuthor Commented:
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, 4

Open in new window

0
PortletPaulfreelancerCommented:
agreeing with above, not sure we can help much as yet, so more information would assist. In the meantime perhaps it's really just a matter of how you filter, something like this:
<!-- AS IS with ansi joins -->

$sql =
"
SELECT
        * /* specify table.fields; i.e. this should be detailed */
FROM acks
INNER JOIN users
        ON acks.userid = users.userid
INNER JOIN files
        ON acks.fileid = files.fileid
WHERE users.deptid = '$currentdept'
        AND users.manager IS NULL
        AND users.adminuser IS NULL
        AND users.supervisor IS NULL
        AND acks.ackbit IS NULL
"
;

<!-- NEW, change method of filtering -->
$sql =
"
SELECT
        * /* specify table.fields; i.e. this should be detailed */
FROM acks
INNER JOIN users
        ON acks.userid = users.userid
INNER JOIN files
        ON acks.fileid = files.fileid
WHERE users.manager = '$currentmanager'
        AND users.adminuser IS NULL
        AND users.supervisor IS NULL
        AND acks.ackbit IS NULL
"
;

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
Ok, the example that you gave is not complete I think, as the data doesn't add up when linked together, but I think based on the structure of the tables and the sample data you're looking for something like this. I apologize for the fact that this was written in MSSQL, not MySQL, but I think the syntax is not very different and should be easily adjustable.

Select		u.[userid] as 'ManagerID'
		,	u.username as 'ManagerName'
		,	md.deptid
		,	d.deptname
		,	a.ackid
		,	a.userid
		,	a.fileid
		,	a.ackbit
		,	a.superid
		,	f.[filename]
		,	f.docname
		,	f.versionid
From	Users u
		--Join users table to managers departments table to retrieve all departments of the manager
		inner join MGRDEPTS md
			on u.userid = md.mgrid
		--Join the deptid from the MGRDEPTS table to the departments table for the department name
		inner join DEPARTMENTS d
			on md.deptid = d.deptid
		--Join the deptid from the manager departments table to a second instance of the users table
		--to get all the users per department
		inner join USERS u2
			on md.deptid = u2.deptid
		--Join the userid from the second instance of the users table to the a ACKS table to get all
		--acknowledgements for all users for all departments under the selected manager
		inner join ACKS a
			on a.userid = u2.userid
		--Join the ACKS fileid to the FILES fileid to get the file details
		inner join FILES f
			on a.fileid = f.fileid
Where	u.userid = 'Insert your parameter for the manager here'

Open in new window


This is assuming that your input parameter is the ID of the manager, who is also a user in the users table. If not you should be able to adapt the "Where" clause to fit your requirements.
Hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hydraziAuthor Commented:
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
0
hydraziAuthor Commented:
STRIKE THAT..... all my stupid fault.  Left a capital letter in the wrong place.
0
hydraziAuthor Commented:
Much appreciate the help getting my query working.  This was right on the money and only needed minor modifications to make this work!  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.