replace the value with different text in Select
-
I have a select statement which joins various tables. But there are two tables which specify nearly same thing but values are different. Because of which when joining its giving more rows than expected. Now I want these two tables to be included in join. like for example QCV_Values table it has Type column as "Oxygen", "NEB" and "Sleep". In another table (arusers) these values are as "HME" and "SLP" in the column "GroupName". "Oxygen" and "NEB" comes under samething "HME". I need to add these tables in to join to avoid duplicacy in the rows. Can any body please help me in achiving this. Except that everything is working fine. Can anybody please help me in that. I am putting my query here And I have to join the Type with the GroupName, but there is no specific table to relate these two.
SELECT InstanceKey, Category, v.QCDesc, oi.CustCenterName, au.MgrName, oi.InsName1, oi.InsName2,
oi.InsName3, oi.TherapyId, oi.PhysNum, oi.TherapyIdSlp, oi.PhysNumSlp, SalesPersonName
FROM dbo.QCV_Instances i
JOIN dbo.vw_OrderIntake oi ON i.InstanceKey=oi.CustNum
--JOIN dbo.Center c ON oi.CustCenterId=c.CenterID
JOIN dbo.arusers au ON oi.CustCenterId=au.CenterID
JOIN dbo.QCV_Values v ON v.QCID=i.QCValueIdThanks & Regards, Md. Abdul Aleem NIIT technologies
-
I have a select statement which joins various tables. But there are two tables which specify nearly same thing but values are different. Because of which when joining its giving more rows than expected. Now I want these two tables to be included in join. like for example QCV_Values table it has Type column as "Oxygen", "NEB" and "Sleep". In another table (arusers) these values are as "HME" and "SLP" in the column "GroupName". "Oxygen" and "NEB" comes under samething "HME". I need to add these tables in to join to avoid duplicacy in the rows. Can any body please help me in achiving this. Except that everything is working fine. Can anybody please help me in that. I am putting my query here And I have to join the Type with the GroupName, but there is no specific table to relate these two.
SELECT InstanceKey, Category, v.QCDesc, oi.CustCenterName, au.MgrName, oi.InsName1, oi.InsName2,
oi.InsName3, oi.TherapyId, oi.PhysNum, oi.TherapyIdSlp, oi.PhysNumSlp, SalesPersonName
FROM dbo.QCV_Instances i
JOIN dbo.vw_OrderIntake oi ON i.InstanceKey=oi.CustNum
--JOIN dbo.Center c ON oi.CustCenterId=c.CenterID
JOIN dbo.arusers au ON oi.CustCenterId=au.CenterID
JOIN dbo.QCV_Values v ON v.QCID=i.QCValueIdThanks & Regards, Md. Abdul Aleem NIIT technologies
You have a serious problem, it's called crappy data! You either create a
CASEin the join that maps each type to another or you create a mapping table that does the same thing. There are pros and cons for both solutions.CASEneeds a change to a procedure if there is a new mapping, map table needs maintenance.Never underestimate the power of human stupidity RAH
-
You have a serious problem, it's called crappy data! You either create a
CASEin the join that maps each type to another or you create a mapping table that does the same thing. There are pros and cons for both solutions.CASEneeds a change to a procedure if there is a new mapping, map table needs maintenance.Never underestimate the power of human stupidity RAH
I concur. But how many times have we all see stuff like this? I like your mapping table solution, unless this is a one off exercise in which case a case statement will run faster and won't need to be maintained.
-
You have a serious problem, it's called crappy data! You either create a
CASEin the join that maps each type to another or you create a mapping table that does the same thing. There are pros and cons for both solutions.CASEneeds a change to a procedure if there is a new mapping, map table needs maintenance.Never underestimate the power of human stupidity RAH