You can use :

Select c.ContactID,

I.IssueID,

'IssueAbout' = 'MEMBER',

MemberID,

MemberName,

from Contact as c

Inner Join Issue as I on I.ContactID = C.ContactID

Inner Join Entity on E.EntityID = I.AboutEntityID

Inner Join EntityType as et...