gistfile1.sql

with typenames as (
	select
		id
		, 'event' kind
		, eventname name
	from events
	
	union all
	
	select
		id
		, 'materials' kind
		, materialname name
	from materials
	
	union all
	
	select
		id
		, 'physician' kind
		, physicianname name
	from physicians
	
	-- repeat for all your different types
)

select
	transactionHistory.transactionObjectTypeID
	, objectType.objectType
	, COALESCE(typenames.name,'default for when type name isnt known') typename
from
	TransactionHistory
inner
	join objectTypes
	on transactionHistory.transactionObjecTypeID = objectTypes.objectID
left outer
	join typenames
	on objectTypes.objectID = typenames.id
	and objectTypes.objectType = typenames.kind