joining on a table twice
-
I have a data table that holds code values, and a status table that maps English values to the code values. Below is a sample table:
dataTable:
CaseNo, Stage1Status, Stage2Status, Stage3Status
1, 2, 2, 3
statusTable:
stageValue, stageName
1, 'Not started'
2, 'Processing'
3, 'Completed'
I want to return a select statement that contains the English values instead of the code values as follows:
1, 'Not started', 'Not started', 'Completed'
So I tried to inner join on the same table twice but obviously that's not allowed. How do I accomplish this?
Thanks in advance!
-
Yes you can join to the same table as many times as you want.
But this is a case where you need to build your table differently :
CaseID
StageID (or StageNumber)
StageStatusID
make that the primary key and your problem is solved.
-
Do you mean I create a CaseID column as a primary key and join on it or do I join on StageID?
-
well you'd only have a single table containing the caseid, what state it's (probabely would take a stage table too) in, and the stagestatusid. Then it's just a simple 2-3 tables join.
-
Other than following Remi's normalization advice,
This is how you would do it as is.
Create table #dataTable (caseNo int, Stage1Status int, Stage2Status int, Stage3Status int)
insert into #datatable(CaseNo, Stage1Status, Stage2Status, Stage3Status)
values(1, 2, 2, 3)
create table #statusTable (StageValue int, StageName varchar(20))
insert into #statusTable (stageValue, stageName)
select 1, 'Not started'
union all
select 2, 'Processing'
union all
select 3, 'Completed'
select CaseNo, S1.StageName, S2.StageName, S3.StageName
from #DataTable
join #StatusTable S1 on Stage1Status = S1.StageValue
join #StatusTable S2 on Stage2Status = S2.StageValue
join #StatusTable S3 on Stage3Status = S3.StageValue
Drop table #DataTable
Drop table #StatusTable
-
SET NOCOUNT ON
DECLARE @dataTable TABLE
(
CaseNo INT,
Stage1Status INT,
Stage2Status INT,
Stage3Status INT
)
INSERT @dataTable
SELECT 1, 2, 2, 3
DECLARE @statusTable TABLE
(
stageValue INT,
stageName VARCHAR(100)
)
INSERT @statusTable
SELECT 1, 'Not started' UNION
SELECT 2, 'Processing' UNION
SELECT 3, 'Completed'
SELECT A.CaseNo,
B.stageName,
C.stageName,
D.stageName
FROM
@dataTable A
JOIN
@statusTable B
ON
A.Stage1Status = B.stageValue
JOIN
@statusTable C
ON
A.Stage2Status = C.stageValue
JOIN
@statusTable D
ON
A.Stage3Status = D.stageValue
Regards,
gova -
As I said it can be done... but when you have 30 objects doing that dance and the manager says, when need to get 6 stages in there instead of 3, you're out the door searching for your mommy
. -
This design will take more stages
SET NOCOUNT ON
DECLARE @dataTable TABLE
(
RowID INT IDENTITY,
CaseNo INT,
StageStatus INT
)
INSERT @dataTable (CaseNo, StageStatus)
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3
DECLARE @statusTable TABLE
(
stageValue INT,
stageName VARCHAR(100)
)
INSERT @statusTable
SELECT 1, 'Not started' UNION ALL
SELECT 2, 'Processing' UNION ALL
SELECT 3, 'Completed'
SELECT CaseNo, stageName
FROM
@dataTable
JOIN
@statusTable
ON
StageStatus = stageValue
WHERE
CaseNo = 1
ORDER BY RowID
DECLARE @Result VARCHAR(1000)
SELECT @Result = COALESCE(@Result + ' ', CONVERT(VARCHAR, CaseNo) + ' - ') + stageName
FROM
@dataTable
JOIN
@statusTable
ON
StageStatus = stageValue
WHERE
CaseNo = 1
ORDER BY RowID
SELECT @Result
Regards,
gova -
It would probabely be a good idea to have dates it that table to know when everything hapenned.
-
Thank you everyone for offering up solutions. I've tested them out and I'm trying to decide which one to use.
Remi, I agree that dates should be included, and they are, but I left them out of the thread for brevity. Could you kindly further explain your normalization solution? I'm not very familiar with the concept and would like to consider it as a possible solution.
-
Your solution is static, the maximum number of stages is 3. For having worked with contract steps, believe me that the list can be very long (seen up to 40). When that happens and you need to add stage4... stage 40, then add 36 joins to all you queries and retest the code... you'll be running for your mom. However if you use the system I propose you can have N stages from 0 to infinity and the application will always work without changing a thing. Also the same thing is true if they want to add data like stage description, stage start date... Then you'd have to add, at least, 6 columns to the design and it's just a pain in the ass to rewrite the application after that.
See where I'm going?
-
If you want to keep your design
you can write a function retrieveStatusName(StatusCode) wich returns StatusName
and use it in the select LIST.
Your performance will be affected by using a select @result=StatusName from StatusTable where StatusCode=@StatusCode
but you ca use a CASE in your function to make it deterministic and your performance will be less affected
set @Result=case @SatusCode
when 1 then 'StatusName1'
....
Kindest Regards,Vasc
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply