if object_id('tempdb..#temp1') is not null
	DROP TABLE [dbo].[#temp1]

if object_id('tempdb..#temp2') is not null
	DROP TABLE [dbo].[#temp2]

SELECT incident.case_ref_pk, incident.inc_no, incident.call_no, incident.inc_datetime, incident.rep_datetime, incident.inc_occurred,
incident.inc_location, incident.arrests_made, incident.bus_name, incident.domesttic_violence, incident.juvenile, report_type.rep_type,
report_type.type_desc,
(SELECT commonprod.dbo.personnel.name from commonprod.dbo.personnel
WHERE commonprod.dbo.personnel.per_id_pk = incident.typed_by_fk) as 'typed_by', 
(SELECT commonprod.dbo.personnel.name from commonprod.dbo.personnel
WHERE commonprod.dbo.personnel.per_id_pk = incident.approved_by_fk) as 'approved_by'
INTO #temp1
FROM incident left outer join report_type on incident.rep_type_fk = report_type.id_pk
WHERE incident.case_ref_pk = @Case_Ref_Pk

SELECT victim.case_ref_fk, rtrim(ltrim(str(vic_id_pk)))+'v' as id_pk, pers_type, name, victim.create_datetime, dob, age, sex, race, soc, weight, height, eye, hair, address, city, state, zip, home_phone, cell_phone, bus_address, bus_city, 
                      bus_state, bus_zip, bus_phone, NULL AS booking_no, pidno_fk, imsoffense.dpsdescription, case when pers_type = 'V' then 1 else (case when pers_type = 'RP'then 2 else 3 end) end as porder
                      INTO #temp2
FROM         victim
left outer join ascoffense on victim.vic_id_pk = ascoffense.sv_id_fk AND ascoffense.type = 'V'
left outer join incoffense on ascoffense.inc_id_fk = incoffense.off_id_pk
left outer join imsoffense on incoffense.offense_code_fk = imsoffense.id_pk
UNION ALL
SELECT   suspect.case_ref_fk, rtrim(ltrim(str(sus_id_pk)))+'s' as id_pk, 'A' AS pers_type, name, suspect.create_datetime, dob, age, sex, race, soc, weight, height, eye, hair, home_address, city, state, zip, home_phone, cell_phone, bus_addr, 
                      bus_city, bus_state, bus_zip, bus_phone, booking_no, pid_fk, imsoffense.dpsdescription,4 as porder
FROM         suspect
left outer join ascoffense on suspect.sus_id_pk = ascoffense.sv_id_fk AND ascoffense.type = 'S'
left outer join incoffense on ascoffense.inc_id_fk = incoffense.off_id_pk
left outer join imsoffense on incoffense.offense_code_fk = imsoffense.id_pk
where suspect.booking_no > 0
UNION ALL
SELECT   suspect.case_ref_fk, rtrim(ltrim(str(sus_id_pk)))+'s' as id_pk, 'S' AS pers_type, name, suspect.create_datetime, dob, age, sex, race, soc, weight, height, eye, hair, home_address, city, state, zip, home_phone, cell_phone, bus_addr, 
                     bus_city, bus_state, bus_zip, bus_phone, booking_no, pid_fk, imsoffense.dpsdescription,5 as porder
FROM         suspect
left outer join ascoffense on suspect.sus_id_pk = ascoffense.sv_id_fk AND ascoffense.type = 'S'
left outer join incoffense on ascoffense.inc_id_fk = incoffense.off_id_pk
left outer join imsoffense on incoffense.offense_code_fk = imsoffense.id_pk
WHERE suspect.booking_no is null or suspect.booking_no = 0
ORDER BY porder ASC, id_pk ASC

SELECT *
FROM #temp1
left outer join #temp2 on #temp1.case_ref_pk = #temp2.case_ref_fk

SELECT *
FROM #temp1 t1
left outer join #temp2 t2 on t1.case_ref_pk = t2.case_ref_fk