Joining 5 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Joining 5 tables

Author

Topic

durexlw
Starting Member

2 Posts

Posted - 2010-06-24 : 16:59:15

Look at this image:

I want to select everything of tblTasks where:
-> on the one hand tblSteps.done = 0
-> on the other hand tblPersons.id = 0
... 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 would be to fill in a form, the second to grand or deny the request.
In the table steps, 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!

sec
Starting Member

4 Posts

Posted - 2010-07-21 : 13:59:19

Here is one direction you might want to pursue:

SELECT
tsk.TaskInfo
FROM
tblTask tsk
WHERE
tsk.TaskID NOT IN (
SELECT
stp.TaskID
FROM
tblStep stp
WHERE
stp.Status = 'Done'
)

Let God be true and every man a liar

Celko
Starting Member

23 Posts

Posted - 2010-07-29 : 14:58:20

>> I want to make a database that contains 'workflows'... Either suggestions on changing this data model to achieve the same or suggestions about solving this query are greatly welcome! <<

First, could you please take those silly "tbl-" prefixes off and follow ISO-11179 naming rules.

First, draw a diagram with boxes [process steps] and arrows [workflows], so you know what you want to do. Look for cycles and sinks, etc. the usual stuff.

Second look at the article at
http://www.simple-talk.com/sql/t-sql-programming/constraint-yourself!/

Your workflow is fancier version of the state change I show there.

CREATE TABLE StateChanges
(previous_state VARCHAR(15) NOT NULL,
current_state VARCHAR(15) NOT NULL,
PRIMARY KEY (previous_state, current_state));

INSERT INTO StateChanges VALUES ('Born', 'Born'), -- initial state
                                ('Born', 'Married'),
                                ('Born', 'Dead'),
                                ('Married', 'Divorced'),
                               ('Married', 'Dead'),
                               ('Divorced', 'Married'),
                               ('Divorced', 'Dead'),
                                ('Dead', 'Dead'); -- terminal state

The target table looks like this.
CREATE TABLE MyLife
(..
previous_state VARCHAR(15) DEFAULT 'Born' NOT NULL,
current_state VARCHAR(15) DEFAULT 'Born' NOT NULL,
FOREIGN KEY (previous_state, current_state)
   REFERENCES StateChanges (previous_state, current_state)
   ON UPDATE CASCADE,
..);

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources