How to get query to extract my own information? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to get query to extract my own information?

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2010-07-28 : 08:34:01

Hi
In my app, to accomplish workflow tasks, i have a few tables to hold workflow data. here is my table definition :

Customers(CustomerID PK, CustomerName) -- for example Alex, Maria, ...
Workflows(WorkflowID PK, WorkflowName) -- for example Vacation Request
WorkflowSteps(WorkflowStepID PK, WorkflowID FK, WorkflowStepName) -- for example Step1, Step2, ...
WorkflowStatus(WorkflowStatusID PK, WorkflowStatusName) -- for exmaple Not Started, Progressing, Completed, Canceled
WorkflowHistory(WorkflowHistoryID PK, CustomerID FK, WorkflowStepID FK, WorkflowStatusID FK)

My question is that how to get select from my data, something like this :
CustomerID, WorkflowID, WorkflowStepID, WorkflowStatusID

notice that, a new customer maybe not have any workflow history record. but all workflowsteps must be listed for any customers (it must be left join with customer)
can anybody help me ?
thanks in advance

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-28 : 10:17:03

Please provide some sample data and expected output too.

Customers and Workflow is only related via the WorkflowHistory?

hdv212
Posting Yak Master

140 Posts

Posted - 2010-07-28 : 11:34:42

Hi
yes, they are related only in history.
here is sample database structure with sample data :

CREATE DATABASE [Test]
GO
USE [Test]
GO
/****** Object: Table [dbo].[WorkflowStatus] Script Date: 07/28/2010 20:02:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WorkflowStatus](
[WorkflowStatusID] [int] NOT NULL,
[WorkflowStatusName] [nvarchar](50) NULL,
CONSTRAINT [PK_WorkflowStatus] PRIMARY KEY CLUSTERED
(
[WorkflowStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[WorkflowStatus] ([WorkflowStatusID], [WorkflowStatusName]) VALUES (1, N'Not Started')
INSERT [dbo].[WorkflowStatus] ([WorkflowStatusID], [WorkflowStatusName]) VALUES (2, N'In Progress')
INSERT [dbo].[WorkflowStatus] ([WorkflowStatusID], [WorkflowStatusName]) VALUES (3, N'Completed')
INSERT [dbo].[WorkflowStatus] ([WorkflowStatusID], [WorkflowStatusName]) VALUES (4, N'Canceled')
/****** Object: Table [dbo].[Workflows] Script Date: 07/28/2010 20:02:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Workflows](
[WorkflowID] [int] NOT NULL,
[WorkflowName] [nvarchar](50) NULL,
CONSTRAINT [PK_Workflows] PRIMARY KEY CLUSTERED
(
[WorkflowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Workflows] ([WorkflowID], [WorkflowName]) VALUES (1, N'Vacation Request')
INSERT [dbo].[Workflows] ([WorkflowID], [WorkflowName]) VALUES (2, N'Request Workflow 2')
/****** Object: Table [dbo].[Customers] Script Date: 07/28/2010 20:02:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
[CustomerID] [int] NOT NULL,
[CustomerName] [nvarchar](50) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Customers] ([CustomerID], [CustomerName]) VALUES (1, N'Alex')
INSERT [dbo].[Customers] ([CustomerID], [CustomerName]) VALUES (2, N'Albert')
INSERT [dbo].[Customers] ([CustomerID], [CustomerName]) VALUES (3, N'Scot')
/****** Object: Table [dbo].[WorkflowSteps] Script Date: 07/28/2010 20:02:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WorkflowSteps](
[WorkflowStepID] [int] NOT NULL,
[WorkflowID] [int] NULL,
[WorkflowStepName] [nvarchar](50) NULL,
CONSTRAINT [PK_WorkflowSteps] PRIMARY KEY CLUSTERED
(
[WorkflowStepID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[WorkflowSteps] ([WorkflowStepID], [WorkflowID], [WorkflowStepName]) VALUES (1, 1, N'Submit Documents')
INSERT [dbo].[WorkflowSteps] ([WorkflowStepID], [WorkflowID], [WorkflowStepName]) VALUES (2, 1, N'Inquiry from Communication')
INSERT [dbo].[WorkflowSteps] ([WorkflowStepID], [WorkflowID], [WorkflowStepName]) VALUES (3, 1, N'Send To Customer')
INSERT [dbo].[WorkflowSteps] ([WorkflowStepID], [WorkflowID], [WorkflowStepName]) VALUES (4, 2, N'Sales Manager Passed')
INSERT [dbo].[WorkflowSteps] ([WorkflowStepID], [WorkflowID], [WorkflowStepName]) VALUES (5, 2, N'CEO Passed')
/****** Object: Table [dbo].[WorkflowHistory] Script Date: 07/28/2010 20:02:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WorkflowHistory](
[WorkflowHistoryID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NULL,
[WorkflowStepID] [int] NULL,
[WorkflowStatusID] [int] NULL,
CONSTRAINT [PK_WorkflowHistory] PRIMARY KEY CLUSTERED
(
[WorkflowHistoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: ForeignKey [FK_WorkflowHistory_Customers] Script Date: 07/28/2010 20:02:13 ******/
ALTER TABLE [dbo].[WorkflowHistory] WITH CHECK ADD CONSTRAINT [FK_WorkflowHistory_Customers] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
GO
ALTER TABLE [dbo].[WorkflowHistory] CHECK CONSTRAINT [FK_WorkflowHistory_Customers]
GO
/****** Object: ForeignKey [FK_WorkflowHistory_WorkflowStatus] Script Date: 07/28/2010 20:02:13 ******/
ALTER TABLE [dbo].[WorkflowHistory] WITH CHECK ADD CONSTRAINT [FK_WorkflowHistory_WorkflowStatus] FOREIGN KEY([WorkflowStatusID])
REFERENCES [dbo].[WorkflowStatus] ([WorkflowStatusID])
GO
ALTER TABLE [dbo].[WorkflowHistory] CHECK CONSTRAINT [FK_WorkflowHistory_WorkflowStatus]
GO
/****** Object: ForeignKey [FK_WorkflowHistory_WorkflowSteps] Script Date: 07/28/2010 20:02:13 ******/
ALTER TABLE [dbo].[WorkflowHistory] WITH CHECK ADD CONSTRAINT [FK_WorkflowHistory_WorkflowSteps] FOREIGN KEY([WorkflowStepID])
REFERENCES [dbo].[WorkflowSteps] ([WorkflowStepID])
GO
ALTER TABLE [dbo].[WorkflowHistory] CHECK CONSTRAINT [FK_WorkflowHistory_WorkflowSteps]
GO
/****** Object: ForeignKey [FK_WorkflowSteps_Workflows] Script Date: 07/28/2010 20:02:13 ******/
ALTER TABLE [dbo].[WorkflowSteps] WITH CHECK ADD CONSTRAINT [FK_WorkflowSteps_Workflows] FOREIGN KEY([WorkflowID])
REFERENCES [dbo].[Workflows] ([WorkflowID])
GO
ALTER TABLE [dbo].[WorkflowSteps] CHECK CONSTRAINT [FK_WorkflowSteps_Workflows]
GO

hdv212
Posting Yak Master

140 Posts

Posted - 2010-07-28 : 18:40:45

Any help ?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-29 : 09:09:42

You still haven't provided complete sample data and expected output.

Refer to this link on how to provide the same

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Here's a shot in the dark....however.

SELECT a.CustomerID
, d.WorkflowID
, b.WorkflowStepID
, b.WorkflowStatusID
from Customers a
LEFT JOIN WorkflowHistory b on a.CustomerID = b.CustomerID
LEFT JOIN WorkflowSteps c on c.WorkflowStepID = b.WorkflowStepID
LEFT JOIN Workflows d on d.WorkflowID = c.WorkflowID

hdv212
Posting Yak Master

140 Posts

Posted - 2010-07-29 : 18:27:50

Thanks vijayisonly
Your query is very useful, but it's result is something like this :

CustomerID WorkflowID WorkflowStepID WorkflowStatusID
1 NULL NULL NULL
2 NULL NULL NULL
3 NULL NULL NULL

and i want result something like this :

CustomerID WorkflowID WorkflowStepID WorkflowStatusID
1 1 1 Unknown
1 1 2 Unknown
1 1 3 Unknown

thanks in advance

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-07-29 : 19:55:05

Your sample schema not matches with information on first post. At the workflowhistory you missed to add the workflowid column. No matter.
I think understand your scenario and expected result.
You need to insert the cross join of the customers and workflowsteps table in workflowhistory.
If I am correct then you need to create an after insert trigger for inserting to workflowhistory. The trigger can be written by this:

create trigger trg_after_insert 
on customers
after insert as
insert workflowhistory (customerid, workflowid, workflowstepid, workflowstatusid)
select customerid, workflowid, workflowstepid, null as workflowstatusid
from Customers, WorkflowSteps w
except 
select customerid, workflowid, workflowstepid, null as workflowstatusid
from workflowhistory;
go
--or

create trigger trg_after_insert 
on customers
after insert as
insert workflowhistory (customerid, workflowid, workflowstepid, workflowstatusid)
select customerid, workflowid, workflowstepid, null as workflowstatusid
from Customers, WorkflowSteps w
where customerid in (select customerid 
                     from inserted);

______________________

hdv212
Posting Yak Master

140 Posts

Posted - 2010-07-30 : 03:19:18

Hi ms65g
Thanks, but i don't want to use trigger. in workflowHistory i have WorkflowStepID which refer to WorkflowSteps table and it have WorkflowID.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources