November 4, 2005 at 1:37 pm
I'm struggling to think of how I need to write this query. I'm pulling a lot of information from a variety of tables and any time I join with a table that will return multiple records, I just need the top 1 listed relating to the main data. My problem area is the body parts and I have bolded them below. If anyone can tell me how to pull just the first body part listed and not all the body parts, I would really appreciate it.
SELECT C.Claim_Number, E.Event_Number, Status.Short_Code Status_Code,
CASE WHEN Status.Short_Code = 'C' THEN 0 ELSE 1 END [Open],
CONVERT(SmallDateTime,C.Date_Of_Claim,101) Date_Of_Claim,
CONVERT(SmallDateTime,E.Date_Reported,101) Date_Reported,
CONVERT(SmallDateTime,LEFT(C.DTTM_Closed,8),101) Date_Closed,
Adj.Last_Name, ENT.Abbreviation Entity_Code, Ent.Last_Name Entity_Name, Dept.Last_Name Department,
E_CL.Last_Name Claimant_Last_Name, E_CL.First_Name Claimant_First_Name, CPI.Short_Code Occupation_Code, CTPI.Code_Desc Occupation,
CONVERT(SmallDateTime,E.Date_Of_Event,101) Accident_Date, E.Time_Of_Event Accident_Time, Date_Of_Death,
PI_WL.Duration Days_Lost, ATC.Code_Desc Accident_Type, LOB.short_code Gen_Class_Code,
ctc.short_code Claim_type_code, ctc.code_desc Claim_type, PRDP1.Short_code PRDP1_Code, PRDP1.Code_Desc PRDP1_Desc,
PRDP2.Short_code PRDP2_Code, PRDP2.Code_Desc PRDP2_Desc, PRDP3.Short_code PRDP3_Code, PRDP3.Code_Desc PRDP3_Desc,
U.Vin, V.Vehicle_make, V.Vehicle_Model, V.Vehicle_Year, PIIC.Short_Code Injury_Code, PIIC.Code_Desc Injury, PIBPC.Short_Code Body_Part_Code, PIBPC.Code_Desc Body_Part
FROM Claim C
LEFT JOIN Event E ON C.Event_ID = E.Event_ID
LEFT JOIN Codes_Text Status ON C.Claim_Status_Code = Status.Code_ID
LEFT JOIN Claim_Adjuster CA ON C.Claim_ID = CA.Claim_ID
LEFT JOIN Entity Adj ON CA.Adjuster_EID = Adj.Entity_ID
LEFT JOIN Org_Hierarchy OH ON E.Dept_EID = OH.Department_EID
INNER JOIN Entity ENT ON OH.Facility_EID = ENT.Entity_ID
INNER JOIN Entity Dept ON OH.Department_EID = Dept.Entity_ID
LEFT JOIN Claimant CL ON C.Claim_ID = CL.Claim_ID
LEFT JOIN Entity E_CL ON CL.Claimant_EID = E_CL.Entity_ID
INNER JOIN Person_Involved P_I ON E.Event_ID = P_I.Event_ID
INNER JOIN Codes CPI ON P_I.Position_Code = CPI.Code_ID
INNER JOIN Codes_Text CTPI ON P_I.Position_Code = CTPI.Code_ID
LEFT JOIN PI_X_Work_Loss PI_WL ON P_I.PI_Row_ID = PI_WL.PI_Row_ID
INNER JOIN Codes_Text ATC ON E.Cause_Code = ATC.Code_ID
INNER JOIN Codes LOB ON C.Line_of_bus_code = LOB.code_ID
INNER JOIN Codes_Text CTC ON C.Claim_type_code = CTC.Code_ID
INNER JOIN Claim_Supp CS ON C.Claim_ID = CS.Claim_ID
INNER JOIN Codes_Text PRDP1 ON CS.PRD1P_Code = PRDP1.Code_ID
INNER JOIN Codes_Text PRDP2 ON CS.PRD2P_Code = PRDP2.Code_ID
INNER JOIN Codes_Text PRDP3 ON CS.PRD3P_Code = PRDP3.Code_ID
LEFT JOIN Unit_X_Claim U ON C.Claim_ID = U.Claim_ID
LEFT JOIN Vehicle V ON V.Unit_ID = U.Unit_ID
INNER JOIN PI_X_Injury PII ON P_I.PI_Row_ID = PII.PI_Row_ID
INNER JOIN Codes_Text PIIC ON PII.Injury_Code = PIIC.Code_ID
LEFT JOIN PI_X_Body_Part PIBP ON P_I.PI_Row_ID = PIBP.PI_Row_ID
LEFT JOIN Codes_Text PIBPC ON PIBP.Body_Part_Code = PIBPC.Code_ID
November 4, 2005 at 8:59 pm
As far as getting only the top 1 record that matches, unless somebody else has a better idea, you're going to need a subquery for each of the line items where your "top 1" is required. Could get fancy and use a UDF but would probably be overkill.
For example where you have PIBPC.Short_Code try:
Short_Code = (select top 1 short_code from PI_X_Body_Part PIBP where P_I.PI_Row_ID = PIBP.PI_Row_ID),
and the drop the left join altogether (LEFT JOIN PI_X_Body_Part PIBP ON P_I.PI_Row_ID = PIBP.PI_Row_ID)
Should do it...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply