May 20, 2015 at 5:24 am
The below is a simplified version of what I am trying to do. I have a job table which has a jobID. The job has multiple gates numbered from 1 to 5. The job can have 1 or more process ids as part of each gate. What I want is, to be able to select a job and see the state of each gate and process id for the job. If the job doesn't have any gate stage information then it should return null values.
Example data:
CREATE TABLE Stages
(Id INT IDENTITY (1,1),
JobId INT,
GateId INT,
ProcessID Int)
INSERT INTO dbo.Stages
( JobId, GateId, ProcessID )
VALUES ( 1,
1,
10
),
( 1,
1,
20
),
( 1,
2,
10
),
( 1,
5,
20
) ,
( 2,
5,
30
)
SELECT *
FROM dbo.Stages
CREATE TABLE Jobs
(Id INT IDENTITY (1,1),
Value VARCHAR(10))
INSERT INTO jobs
VALUES
('Job 1'),('Job 2'), ('Job 6')
SELECT * FROM Jobs
If I execute the following code, I get kind of what I want, but I should only have Job 1 with Process ID 20 for gate 5, but a value is put in to gate 5 for job 1 process ID 10. Gate 5, process ID 10 for job 1 doesn't exist in the stages table.
SELECT Jobs.Id ,
Jobs.Value ,
Gate1.GateId AS Gate1ID ,
Gate1.ProcessID AS Gate1ProcessId ,
Gate2.GateId AS Gate2ID ,
Gate2.ProcessID AS Gate2ProcessId ,
Gate3.GateId AS Gate3ID ,
Gate3.ProcessID AS Gate3ProcessId ,
Gate4.GateId AS Gate4ID ,
Gate4.ProcessID AS Gate4ProcessId ,
Gate5.GateId AS Gate5ID ,
Gate5.ProcessID AS Gate5ProcessId
FROM dbo.Jobs
LEFT OUTER JOIN ( SELECT Stages.JobId ,
Stages.GateId ,
Stages.ProcessID
FROM Stages
JOIN dbo.Jobs ON Jobs.Id = Stages.JobId
WHERE GateId = 1
) AS Gate1 ON Gate1.JobId = dbo.Jobs.Id
LEFT OUTER JOIN ( SELECT Stages.JobId ,
Stages.GateId ,
dbo.Stages.ProcessID
FROM Stages
JOIN dbo.Jobs ON Jobs.Id = Stages.JobId
WHERE GateId = 2
) AS Gate2 ON Gate2.JobId = dbo.Jobs.Id
LEFT OUTER JOIN ( SELECT Stages.JobId ,
Stages.GateId ,
dbo.Stages.ProcessID
FROM Stages
JOIN dbo.Jobs ON Jobs.Id = Stages.JobId
WHERE GateId = 3
) AS Gate3 ON Gate3.JobId = dbo.Jobs.Id
LEFT OUTER JOIN ( SELECT Stages.JobId ,
Stages.GateId ,
dbo.Stages.ProcessID
FROM Stages
JOIN dbo.Jobs ON Jobs.Id = Stages.JobId
WHERE GateId = 4
) AS Gate4 ON Gate4.JobId = dbo.Jobs.Id
LEFT OUTER JOIN ( SELECT Stages.JobId ,
Stages.GateId ,
dbo.Stages.ProcessID
FROM Stages
JOIN dbo.Jobs ON Jobs.Id = Stages.JobId
WHERE GateId = 5
) AS Gate5 ON Gate5.JobId = dbo.Jobs.Id;
I need to be able to join on the process ID column I think but I cannot work it out.
Any pointers would be great.
May 20, 2015 at 5:44 am
Like this?
SELECT j.*,
Gate1ID = s1.GateId, Gate1ProcessID = s1.ProcessID,
Gate2ID = s2.GateId, Gate2ProcessID = s2.ProcessID,
Gate3ID = s3.GateId, Gate3ProcessID = s3.ProcessID,
Gate4ID = s4.GateId, Gate4ProcessID = s4.ProcessID,
Gate5ID = s5.GateId, Gate5ProcessID = s5.ProcessID
FROM #Jobs j
LEFT JOIN #Stages s1 ON s1.JobId = j.Id AND s1.GateId = 1
LEFT JOIN #Stages s2 ON s2.JobId = j.Id AND s2.GateId = 2
LEFT JOIN #Stages s3 ON s3.JobId = j.Id AND s3.GateId = 3
LEFT JOIN #Stages s4 ON s4.JobId = j.Id AND s4.GateId = 4
LEFT JOIN #Stages s5 ON s5.JobId = j.Id AND s5.GateId = 5
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 20, 2015 at 5:58 am
Or like this?
SELECT j.*,
Gate1ID = (CASE WHEN s.GateId = 1 THEN 1 ELSE NULL END), Gate1ProcessID = (CASE WHEN s.GateId = 1 THEN s.ProcessID ELSE NULL END),
Gate2ID = (CASE WHEN s.GateId = 2 THEN 2 ELSE NULL END), Gate2ProcessID = (CASE WHEN s.GateId = 2 THEN s.ProcessID ELSE NULL END),
Gate3ID = (CASE WHEN s.GateId = 3 THEN 3 ELSE NULL END), Gate3ProcessID = (CASE WHEN s.GateId = 3 THEN s.ProcessID ELSE NULL END),
Gate4ID = (CASE WHEN s.GateId = 4 THEN 4 ELSE NULL END), Gate4ProcessID = (CASE WHEN s.GateId = 4 THEN s.ProcessID ELSE NULL END),
Gate5ID = (CASE WHEN s.GateId = 5 THEN 5 ELSE NULL END), Gate5ProcessID = (CASE WHEN s.GateId = 5 THEN s.ProcessID ELSE NULL END)
FROM #Jobs j
LEFT JOIN #Stages s ON s.JobId = j.Id
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 20, 2015 at 6:26 am
Thanks for the help.
Your first query, shows the same results as my query - the problem being for Gate5, where the job id is 1 and process id is 10, the gate 5 columns should be null, but they get set to a process id of 20.
The second query, works, but splits out the gate values to different rows. So I have a row for each result for each job id. I need just the unuqie rows for each job based on process id.
I think the issue is that this is a many to many table join and I need to be able to compare each join with each of the other joins and get the common values. I.E Job 1 Gate 1 Process ID 10 should also have results in the same row as where job 1 has a gate id 2 and process id 10. But there should be a second row for Job 1 , Gate 1, process 20 with all the other columns being null.
Hope that makes sense.
May 20, 2015 at 7:23 am
OK, think I've got it now:
SELECT j.*,
s1.Gate1ID, s1.Gate1ProcessId,
s2.Gate2ID, s2.Gate2ProcessId,
s3.Gate3ID, s3.Gate3ProcessId,
s4.Gate4ID, s4.Gate4ProcessId,
s5.Gate5ID, s5.Gate5ProcessId
FROM #Jobs j
LEFT JOIN (
SELECT JobId, n
FROM (
SELECT JobId, cnt = MAX(cnt)
FROM (
SELECT JobId, GateID, cnt = COUNT(*)
FROM #Stages
GROUP BY JobId, GateID
) d
GROUP BY JobId
) e
CROSS APPLY (VALUES (1),(2)) iTally (n)
WHERE n <= cnt
) m ON m.JobId = j.Id
LEFT JOIN (
SELECT rn = ROW_NUMBER() OVER(PARTITION BY JobId, GateID ORDER BY ProcessID),
JobId, Gate1ID = GateId, Gate1ProcessId = ProcessID
FROM #Stages WHERE GateId = 1
) s1 ON s1.JobId = m.JobID AND s1.rn = m.n
LEFT JOIN (
SELECT rn = ROW_NUMBER() OVER(PARTITION BY JobId, GateID ORDER BY ProcessID),
JobId, Gate2ID = GateId, Gate2ProcessId = ProcessID
FROM #Stages WHERE GateId = 2
) s2 ON s2.JobId = m.JobID AND s2.rn = m.n
LEFT JOIN (
SELECT rn = ROW_NUMBER() OVER(PARTITION BY JobId, GateID ORDER BY ProcessID),
JobId, Gate3ID = GateId, Gate3ProcessId = ProcessID
FROM #Stages WHERE GateId = 3
) s3 ON s3.JobId = m.JobID AND s3.rn = m.n
LEFT JOIN (
SELECT rn = ROW_NUMBER() OVER(PARTITION BY JobId, GateID ORDER BY ProcessID),
JobId, Gate4ID = GateId, Gate4ProcessId = ProcessID
FROM #Stages WHERE GateId = 4
) s4 ON s4.JobId = m.JobID AND s4.rn = m.n
LEFT JOIN (
SELECT rn = ROW_NUMBER() OVER(PARTITION BY JobId, GateID ORDER BY ProcessID),
JobId, Gate5ID = GateId, Gate5ProcessId = ProcessID
FROM #Stages WHERE GateId = 5
) s5 ON s5.JobId = m.JobID AND s5.rn = m.n
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 20, 2015 at 8:13 am
Pefect! That's what I need. Thank you so much.
May 20, 2015 at 8:25 am
Maddave (5/20/2015)
Pefect! That's what I need. Thank you so much.
You're welcome. Don't settle for the first attempt though;
Make it work (see above)
Make it fast (see next query)
Make it pretty - that's documentation, and that's your job - so long as you understand the query.
Here's the fast version:
SELECT j.*,
s.Gate1ID, s.Gate1ProcessId,
s.Gate2ID, s.Gate2ProcessId,
s.Gate3ID, s.Gate3ProcessId,
s.Gate4ID, s.Gate4ProcessId,
s.Gate5ID, s.Gate5ProcessId
FROM #Jobs j
LEFT JOIN(
SELECT JobID, rn,
Gate1ID = MAX(CASE WHEN GateID = 1 THEN 1 END), Gate1ProcessId = MAX(CASE WHEN GateID = 1 THEN ProcessID END),
Gate2ID = MAX(CASE WHEN GateID = 2 THEN 2 END), Gate2ProcessId = MAX(CASE WHEN GateID = 2 THEN ProcessID END),
Gate3ID = MAX(CASE WHEN GateID = 3 THEN 3 END), Gate3ProcessId = MAX(CASE WHEN GateID = 3 THEN ProcessID END),
Gate4ID = MAX(CASE WHEN GateID = 4 THEN 4 END), Gate4ProcessId = MAX(CASE WHEN GateID = 4 THEN ProcessID END),
Gate5ID = MAX(CASE WHEN GateID = 5 THEN 5 END), Gate5ProcessId = MAX(CASE WHEN GateID = 5 THEN ProcessID END)
FROM (
SELECT rn = ROW_NUMBER() OVER(PARTITION BY JobId, GateID ORDER BY ProcessID),
JobId, GateID, ProcessID
FROM #Stages
) d
GROUP BY JobID, rn
) s ON s.JobId = j.Id
ORDER BY j.Id
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 20, 2015 at 9:14 am
Even better! Thanks.
I understand the second query more than the first, but will study it in more detail to understand how it is working.
Thanks again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply