How can I query multiple many-to-many relationships in the same result set?
I have two tables that I typically always LEFT JOIN for a standard result set:
tblPROJECTS-
id | jobnumber | jobname ...
--------------------------------------------------
1 | 1000 | Project X
2 | 2000 | Project Y
3 | 3000 | Project Z
tblTASKS-
id | tasknumber | jobnumber | taskname ...
--------------------------------------------------
1 | 10 | 1000 | Project X: Task 1
2 | 20 | 1000 | Project X: Task 2
3 | 30 | 2000 | Project Y: Task 1
Tasknumber is a GUID, independent of jobnumber, but will never be related to more than one job.
I LEFT JOIN tblTASKS on jobnumber, since not all projects will have tasks (yet)
But then I also have an owners table that defines 1-n users who own either the job as a whole or the individual tasks (or both). Each user can own multiple jobs and/or tasks. The original design of the DB spec'd that a single table be used.
tblOWNERS-
id | ownertype | ownerid | jobnumber | tasknumber ...
----------------------------------------------------------------
1 | 1 | 2 | 1000 |
2 | 1 | 4 | 1000 |
3 | 2 | 2 | | 10
An ownertype of 1 indicates the user owns the overall job. An ownertype of 2 indicates the user owns the task within the job.
I have two queries that I'm trying to construct:
1) Return the job with all associates job owners, joined with all tasks for that job with all associated task owners.
jobnumber | jobowners | tasknumber | taskowners ...
1000 | 2,4,... | 10 | 2
2000 | | 20 | 4,6,8...
3000 | 4,5,6... | 30 |
2) Given an owner ID, return all the jobs and/or tasks they are associated with.
It's the multiple many-to-many from/to the same tables that has me stumped. Can I accomplish this? If so, am I looking for some sort of UNION or INTERSECT (what do I look up to learn)? Or, if not, what's the better schema for relationships like this that would allow for it?
TIA!
Generally, you need to place the foreign key in the many end of an ERD, so in this case you might have a field called 'ownerid' in the table 'tblPROJECTS', as well as having 'ownerid' in tblTASKS. Assuming then that all tasks have a job ID and an owner, and all projects also have an owner, you can use INNER JOINs:
SELECT P.jobnumber,T.tasknumber,O1.id AS taskowner,O2.id AS jobowner
FROM tblTASKS T
INNER JOIN tblPROJECTS P ON P.jobnumber=T.jobnumber
INNER JOIN tblOWNERS O1 ON O1.id=T.ownerid
INNER JOIN tblOWNERS O2 ON O2.id=P.ownerid
WHERE O1.id=1
This will not concatenate the jobowners and task owners as you have described, but will return a row for each, which you can then concatenate whilst processing the resultset.
Then just replace the WHERE clause as necessary to get the list of tasks for a given Job number...
WHERE P.jobnumber=1000