Joining 5 tables

[durexlw]

Look at this image:

http://www.roflsaurus.com/users/durexlw_QuickUL/SQL_join5Tables.jpg

I want to select everything of tblPersons where all taskorders are connected to that person and where tblSteps.done = 0 and where tblPersons.id = a certain ID... needless to say: my head hurts and I can't seem to figure this one out. Any help is greatly appreciated.

-

The point of this datamodel is:
I want to make a database that contains 'workflows':
For example if I get a task (tblTasks) for a hollidayrequest, the workflow (tblWorkflows) to complete that task will contain two steps (tblSteps): the first step will be to fill in a form, the second step will be to grant that vacation or not and give a message why.
In that table tblSteps, for each step will be parameters and the field 'done' will indicate what step we're at in the workflow.
Differently said: to see what is on my personal todo list, I have to find the above querry.

Either suggestions on changing this datamodel to achieve the same or suggestions about solving this querry are greatly welcome!

[rch1231]

Hello,

My best suggestion is to take it one join at a time and watch the results. Can you post your current query here?

In the mean time based on what you said in your post I would start the query in the tblTasks table and pull your data from there. I don't see any place where you list what the current step is for the task. If you had a field in tbl.Task that showed the current task step called "CurrentStepId":

Select tblTask.ID as Task_ID,
tblTask.taskName as Task_Name,
tblPersons.lastName as Last_Name
from tblTask 
INNER JOIN on tblWorkflow where tblTask.ID = tblWorkflow.taskIdFK 
INNER JOIN on tblTaskAssignment where tblTask.ID = tblTaskAssignment.TaskIdFK
where tblTask.CurrentStepId = 0

should get you started....

[tesuji]

Greetings from the hood

First, to your question how to join 5 tables:

-- To get all steps of a person which are assigned via TaskOrders:
select lastname from Persons p join taskorders o ON p.ID = o.personaidFK
  join tasks t ON t.ID = o.taskidFK join workflow w ON w.taskidFK = t.ID
    join steps s ON s.ID = w.stepidFK  
       where p.ID = your_current_personal_ID;

-- This pretty construct could be reduced to:
select lastname from Persons p join taskorders join tasks join workflow
    join steps where p.ID = your_current_personal_ID;
-- if all primary and foreign keys constraints were properly set up.

If you want to know which steps of a workflow are undone you must add a further attribute, say status, to table workflow (status = 0--> undone, status = 1--> finished).

-- Then your select would look like
select lastname from Persons p join taskorders o ON p.ID = o.personaidFK
  join tasks t ON t.ID = o.taskidFK join workflow w ON w.taskidFK = t.ID
    join steps s ON s.ID = w.stepidFK 
       where p.ID = your_current_personal_ID AND w.status = 0;

-- or simpler
select lastname from Persons p join taskorders join tasks join workflow w
    join steps where p.ID = your_current_personal_ID AND w.status = 0;

Second, obviously there are some discrepancies in your data model which can possibly be unveiled by those questions:
1. Can a certain task be assigned to various persons? (Yes), dito orders.
2. Can a certain task be assigned to various steps? (yes).
3. Can a certain task be assigned to the same steps a half year later? (no), dito person to tasks via assignment or order.
4. Is there any order given to proccessing steps, e.g. step 1 then step 2 etc? (no).

5. Most important: What if you need a further triple (param03Table,param03Name, param03Value) in steps?
Consequence: Structure of table steps must be changed, also the select statement and the complete program which process this additional data. In other words: Because of its horizontal repeating group table steps does not meet 1NF. btw, you can't create effective query statements on such a wrong table structure. Try it and you will be very surprised. :@

6. What is the difference between taskassignment and taskorder? Could those tables not be aggregate to only one table, say personstask, where an attribute, say asor, is to distinguish between them?
7. What means "done" in steps?
8. Why prefixing all table with "tbl" ?

-- tesu

[durexlw]

Hello,

My best suggestion is to take it one join at a time and watch the results. Can you post your current query here?

Thanks for your suggestions, I appreciate it., I don't have it here:
The model has changed as well, I gave it some further thought last week and many holes showed up. I'll post the query and the changes on monday.

[durexlw]

Greetings from the hood

First, to your question how to join 5 tables:

-- To get all steps of a person which are assigned via TaskOrders:
select lastname from Persons p join taskorders o ON p.ID = o.personaidFK
  join tasks t ON t.ID = o.taskidFK join workflow w ON w.taskidFK = t.ID
    join steps s ON s.ID = w.stepidFK  
       where p.ID = your_current_personal_ID;

-- This pretty construct could be reduced to:
select lastname from Persons p join taskorders join tasks join workflow
    join steps where p.ID = your_current_personal_ID;
-- if all primary and foreign keys constraints were properly set up.

Thanks man, you strike me as very knowledgeable about this topic. I'm feeling honored with your help and I appreciate the challenges you give me here: it helps me think and expands my view in many ways.

If you want to know which steps of a workflow are undone you must add a further attribute, say status, to table workflow (status = 0--> undone, status = 1--> finished).

Adding it to the table workflow wouldn't cut it: if one person would start a certain task (add an entry to tblTaskOrders) and another person would give an order for that same task, then having a steps in the wokflow table, would result in setting all those tasks to that certain step at once. The 'steps' field has to be in the taskorders table.

For example: I have a task 'bookrequest' where the workflow consists of two steps: filling in a form and getting back a mail.
Person A gives a taskOrder (launces a task; tblTaskOrders), completing the first step: filling in a form.
Person B launches that same task, filling in the same form.
Person C is assigned (tblTaskAssignments) to handle all 'bookorders'.
If Person C then sends a mail to person A, completig the task, setting the 'steps' to 1, suddenly Person B's task would appear to be completed as well... which obviously isn't the case.

7. What means "done" in steps?

It's an error: it was an attempt to what you pointed out in the previous part. I now see this field should be located in 'tblTaskOrders'.

6. What is the difference between taskassignment and taskorder? Could those tables not be aggregate to only one table, say personstask, where an attribute, say asor, is to distinguish between them?

Just so we're on the same page:
Imagine a company: there are tasks that should be done (tblTaskOrders) and there are people who are assigned to do those tasks (tblTaskAssignments).

Given some people will quit, and other will change work or whatever, I need that table to assign tasks to another person on the fly... all running tasks should be switched to the new person as well. This is the function of the table 'tblTaskAssignments'.
An error in this current model is that one specific task can only be given once by a certain person. Obviously one person should be able to start the same task more than once.

The point here is: a person can start a task, or a person can be assigned to complete certain given tasks.

It somewhat made sense building two tables for those. What would be the advantage of putting this all in one table?

Second, obviously there are some discrepancies in your data model which can possibly be unveiled by those questions:
1. Can a certain task be assigned to various persons? (Yes), dito orders.

A certain task should be able to be tackled by many people: a team or a group.

2. Can a certain task be assigned to various steps? (yes).

I'm not sure what you mean by this. I'm assiming you see a problem in this I fail to find, would you be willing to point how this would become a problem?

3. Can a certain task be assigned to the same steps a half year later? (no), dito person to tasks via assignment or order.

Look at the table 'Tasks' as a static thing: a task is a workflow in the sence that a certain sequence of steps should be completed in order to complete a task. In a company a task will stay the same and if it changes workflow (i.e. uses different steps or different sequence of steps) then this should be changed for all running tasks as well.

So a task should not be assigned to steps a (half) year later. What can happen is that a person launces another taskOrder: i.e. gives the order to start a certain task (which is a sequence of steps, defined in the workflows table). Therefore I believe the 'status' field you suggested earlier should be in the table TaskOrders: a person could then launch a task of all defined tasks a company is assigned/concerned with.

4. Is there any order given to proccessing steps, e.g. step 1 then step 2 etc? (no).

'order' is indeed a required field in workflows, allowing admins to switch order of steps easily.

5. Most important: What if you need a further triple (param03Table,param03Name, param03Value) in steps?
Consequence: Structure of table steps must be changed, also the select statement and the complete program which process this additional data. In other words: Because of its horizontal repeating group table steps does not meet 1NF. btw, you can't create effective query statements on such a wrong table structure. Try it and you will be very surprised. :@

This is indeed one of the things I'm struggling with most. I think I should give some more background as to what is my intention exactly:

We're in a company: there are tasks and tasks are a sequence of certain steps. There are many different steps and a same step (filling in a form for example) will be used in many different workflows.
Take the example of filling in a form:
param01Table would be tblForms (table containing all forms)
param01Name would be the identifier of that param01table. Differently said: "what field should I pick from that table?"... 'ID' for example.
param01Value would be (in this example) the specific ID, pointing to the exact form in my forms table.

Likewise: another step could be to send an email, all mails could be stored in the mails table and I could then recycle mails.

Come to think about it: you are right that I'd much better add a way of dynamically allow paramsXX to grow. I'm not sure how I can go about this... you have any suggestions?

8. Why prefixing all table with "tbl" ?

M$ Access habbit... it helps me reduce errors and increase coding speed: tblPersons would probably have a frmPersons (a form). Like this I always know what exactly I'm dealing with and I can have sensible names that suggest relations between the table and the form... in SQL I'd see no use doing that.

[durexlw]

Can you post your current query here?

SELECT tblTaskAssignments.personIdFK, tblTaskOrders.taskIdFK, tblTasks.taskName, tblSteps.stepName, tblTaskOrders.personIdFK
FROM (
tblTasks LEFT JOIN (tblSteps RIGHT JOIN tblWorkflows ON tblSteps.ID = tblWorkflows.stepIdFK) 
  ON tblTasks.ID = tblWorkflows.taskIdFK)
RIGHT JOIN  (tblTaskOrders LEFT JOIN tblTaskAssignments ON tblTaskOrders.taskIdFK = tblTaskAssignments.taskIdFK)
  ON tblTasks.ID = tblTaskOrders.taskIdFK
WHERE tblSteps.done = FALSE

The datamodel changed to the following:
http://www.roflsaurus.com/users/durexlw_QuickUL/Database - Workflows.jpg

The field 'done' in tblSteps is obsolete as discussed above and the paramXX need a different solution.

[rch1231]

I take it you are still not getting the result you expected...

The best suggestion I can make from here is to break it down to one join at a time and review the results. start with the first table and make sure you are pulling the records from there you want to start with.

SELECT tblSteps.stepName,
FROM tblSteps 
WHERE tblSteps.done = FALSE

then add the first join and recheck:

SELECT  tblSteps.stepName, 
tblWorkflows.stepIdFK
FROM tblSteps 
RIGHT JOIN tblWorkflows ON 
tblSteps.ID = tblWorkflows.stepIdFK  
  WHERE tblSteps.done = FALSE

And keep going till the results fail.

[durexlw]

I take it you are still not getting the result you expected...

The best suggestion I can make from here is to break it down to one join at a time and review the results. start with the first table and make sure you are pulling the records from there you want to start with.

SELECT tblSteps.stepName,
FROM tblSteps 
WHERE tblSteps.done = FALSE

then add the first join and recheck:

SELECT  tblSteps.stepName, 
tblWorkflows.stepIdFK
FROM tblSteps 
RIGHT JOIN tblWorkflows ON 
tblSteps.ID = tblWorkflows.stepIdFK  
  WHERE tblSteps.done = FALSE

And keep going till the results fail.

The query I gave works. I appreciate the suggestion non the less.

You happen to have some thoughts about the things tesuji raised?

[tesuji]

Hi durexlw,

attached is an ERM which I just finshed while watching ESP vs. POR. It is mainly focused on the problem of your tblWorkflows. I think the solution consisting of Task, Workflow and WorkflowDescription is able to manage as many as possible pairs (paramjjTable, paramjjValue), and most important there is no further need to change sql statements nor to changing a table's schema if additional pairs (table, value) come out.

Here is a query which works on that ERM (however not tested): List all Persons with assigned tasks and their associated workflows not as yet finished:

select p.name as Person, Datim as "Date", t.name as Task, Step, Table, Value
  from Persno p join TaskAssignment join Task t join Workflow join WorkflowDescription
    where Status = 0 order by taskID, wfdID, step;

I left open the ON-clauses for they are not necessary if all primary and foreign keys are properly defined (sure, it depends on the notion of the database designer whether ON is needed. But one should taken into account that every time one needs to quote an ON this could be a signal pointing to a lack of referential integrity in his data model. On the other hand naturally developed data models usually lacks of consistent names for primary and foreign keys, therefore ON-clauses are quite usual).

btw, I am also no that certain, that your lastly posted query would always work perfectly.

-- tesu

[durexlw]

Hey tesuji,

I totally agree with what you come up here:
I've been doodeling yesterday and I came to the same result with only one difference: I placed the 'status' field in the TaskOrder-table, otherwise when two people launch one task, the status is not unique for each person.

I very much appreciate you feedback. I've enjoyed it to have my ideas challenged and improved. Equally I welcome the little bits of extra info you give here and there. Thanks!