January 26, 2015 at 12:50 pm
Project has 2 tables process(parent) and processchild(child).
Project workflow recorddsany changes to these tables as a history.
I want to find out all the process that are in status = saved(1) where processchild is at status = started(1).
Here is example.
Process table
PK, processid, status , other data
1, 1, 1,...
2, 1, 2,...
3, 2, 1,...
4, 3, 1,...
ProcessChild table
PK, processid, processchildid status, other data
1, 1, 1, 1,..
2, 1, 1, 2,..
3, 1, 2, 1,...
4, 1, 2, 2,...
5, 2, 1, 1,..
6, 2, 1, 2,...
7, 2, 2, 1,...
8, 3, 1, 1,..
I want to find out all the processes where processchildid=2 and processchild.status =1
January 26, 2015 at 1:47 pm
Saujib (1/26/2015)
Project has 2 tables process(parent) and processchild(child).Project workflow recorddsany changes to these tables as a history.
I want to find out all the process that are in status = saved(1) where processchild is at status = started(1).
Here is example.
Process table
PK, processid, status , other data
1, 1, 1,...
2, 1, 2,...
3, 2, 1,...
4, 3, 1,...
ProcessChild table
PK, processid, processchildid status, other data
1, 1, 1, 1,..
2, 1, 1, 2,..
3, 1, 2, 1,...
4, 1, 2, 2,...
5, 2, 1, 1,..
6, 2, 1, 2,...
7, 2, 2, 1,...
8, 3, 1, 1,..
I want to find out all the processes where processchildid=2 and processchild.status =1
What is the exact relationship between the two tables?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 26, 2015 at 3:02 pm
Sample create table statements, sample data and expected output please. Then we can help you more efficiently and with less chance of error.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 26, 2015 at 8:02 pm
Do you mean this?
WITH ProcessTable (PK, processid, status) AS
(
SELECT 1, 1, 1
UNION ALL SELECT 2, 1, 2
UNION ALL SELECT 3, 2, 1
UNION ALL SELECT 4, 3, 1
),
ProcessChildTable (PK, processid, processchildid, status) AS
(
SELECT 1, 1, 1, 1
UNION ALL SELECT 2, 1, 1, 2
UNION ALL SELECT 3, 1, 2, 1
UNION ALL SELECT 4, 1, 2, 2
UNION ALL SELECT 5, 2, 1, 1
UNION ALL SELECT 6, 2, 1, 2
UNION ALL SELECT 7, 2, 2, 1
UNION ALL SELECT 8, 3, 1, 1
)
SELECT *
FROM ProcessTable a
JOIN ProcessChildTable b ON a.processid = b.processid
WHERE b.processchildid=2 AND b.status = 1;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply