How to join subquery and another table to receive extra data

Hi,

I have query:

SELECT
A.Issue
,(select IssueAttributeValue from dimIssueAttributes where Issue = A.Issue and IssueAttributeName = 'summary') as Temat
,A.issuetype as Rodzaj_Zgloszenia
,(select top 1 [dbo].[fGetFromJsonV1]([dbo].[fGetFromBracketV2](IssueAttributeValue),'value') from dimIssueAttributes A2 where A.Issue = A2.Issue and A2.IssueAttributeName = 'customfield_12902') as Kod_zamknięcia
,(select dbo.fGetFromJsonV1(IssueAttributeValue,'name') from dimIssueAttributes k2 where A.Issue = k2.Issue and k2.IssueAttributeName= 'resolution') as Rozwiązanie
,(select dbo.fGetFromJsonV1(dbo.fGetFromJsonV1(IssueAttributeValue,'requestType'),'name') from dimIssueAttributes E where A.Issue = E.Issue and IssueAttributeName = 'customfield_10116') as Customer_request_type
,A.status as Status
,A.Created as Czas_utworzenia
,(select dbo.fGetDate(IssueAttributeValue) from dimIssueAttributes I where A.Issue = I.Issue and IssueAttributeName = 'customfield_12506') as Czas_usunięcia_awarii
,(select dbo.fGetDate(IssueAttributeValue) from dimIssueAttributes C2 where A.Issue = C2.Issue and IssueAttributeName = 'resolutiondate') as Czas_rozwiązania
,(select dbo.fsCzasRozwiazaniaIncydentV1(cs1.Issue) from dimIssue cs1 where A.Issue = cs1.Issue) as Całkowity_czas_realizacji
,(select dbo.fGetFromJsonV1(IssueAttributeValue,'name') from dimIssueAttributes G where A.Issue = G.Issue and G.IssueAttributeName = 'customfield_11221') as Grupa_Odpowiedzialna
,(select dbo.fGetRSPOv2(IssueAttributeValue) from dimIssueAttributes ia where A.issue = ia.Issue and ia.IssueAttributeName = 'summary' ) as Lista_RSPO
,(select IssueAttributeValue from dimIssueAttributes D2 where A.Issue = D2.Issue and D2.IssueAttributeName = 'customfield_12109') as ID_2017
,(select IssueAttributeValue from dimIssueAttributes W where A.issue = W.issue and IssueAttributeName = 'customfield_12231') as Województwo
,(select IssueAttributeValue from dimIssueAttributes E2 where A.Issue = E2.Issue and IssueAttributeName = 'customfield_19500') as Lokalizacja
,(select IssueAttributeValue from dimIssueAttributes L (nolock) where L.issue = A.Issue and L.issueattributename = 'customfield_12113') as Lokacja_POPC
,(select IssueAttributeValue from dimIssueAttributes Y where Y.issue = A.Issue and Y.issueattributename = 'customfield_21612') as Podwykonawca
,(select IssueAttributeValue from dimIssueAttributes T where A.issue = T.issue and IssueAttributeName = 'customfield_11510') as Operator_łącza_abonenckiego
,(select IssueAttributeValue from dimIssueAttributes P where A.issue = P.issue and IssueAttributeName = 'customfield_12406') as Network_Link
-- Informacja czy jest przydzielone zgłoszenie OSETT
,(select case when exists(select top 1 IssueRelate from dimIssueRelateTo B1 where A.Issue = B1.Issue and B1.Issue like '%OSESD%' and B1.IssueRelate like '%OSETT%') then 'Tak' else 'Nie' end) as Czy_była_awaria
,(select IssueAttributeValue from dimIssueAttributes H2 where A.Issue = H2.Issue and H2.IssueAttributeName = 'customfield_21612') as Podwykonawca
-- Tech_ID Usługa dla Szkoły
,(select IssueAttributeValue from dimIssueAttributes U where A.issue = U.issue and IssueAttributeName = 'customfield_11404') as Tech_ID
,(select [dbo].[fGetIdObjectV2](IssueAttributeValue) from dimIssueAttributes ds1 where A.Issue = ds1.Issue and IssueAttributeName = 'customfield_11404') as Object_Key_Usługa
,dbo.fZegarState(B.issueAttributeValue) as 'CR_fZegarState'
,dbo.fZegarPrzekroczenie(B.issueAttributeValue) as 'CR_fZegarPrzekroczenie'
,dbo.fZegarSpendTime(B.issueAttributeValue) as 'CR_fZegarSpendTime'
,dbo.fZegarPozostalyCzas(B.issueAttributeValue) as 'CR_fZegarPozostalyCzas'
,dbo.fZegarPrzekroczenieCzas(B.issueAttributeValue) as 'CR_fZegarPrzekroczenieCzas'
,dbo.fZegarState(C.issueAttributeValue) as 'CRo_fZegarState'
,dbo.fZegarPrzekroczenie(C.issueAttributeValue) as 'CRo_fZegarPrzekroczenie'
,dbo.fZegarSpendTime(C.issueAttributeValue) as 'CRO_fZegarSpendTime'
,dbo.fZegarPozostalyCzas(C.issueAttributeValue) as 'CRo_fZegarPozostalyCzas'
,dbo.fZegarPrzekroczenieCzas(C.issueAttributeValue) as 'CRo_fZegarPrzekroczenieCzas'
,dbo.fZegarState(D.issueAttributeValue) as 'CK_fZegarState'
,dbo.fZegarPrzekroczenie(D.issueAttributeValue) as 'CK_fZegarPrzekroczenie'
,dbo.fZegarSpendTime(D.issueAttributeValue) as 'CK_fZegarSpendTime'
,dbo.fZegarPozostalyCzas(D.issueAttributeValue) as 'CK_fZegarPozostalyCzas'
,dbo.fZegarPrzekroczenieCzas(D.issueAttributeValue) as 'CK_fZegarPrzekroczenieCzas'
FROM dbo.dimIssue A
left join dbo.dimIssueAttributes B on A.Issue = B.Issue and B.IssueAttributeName = 'customfield_12906'
left join dbo.dimIssueAttributes C on A.Issue = C.Issue and C.IssueAttributeName = 'customfield_12913'
left join dbo.dimIssueAttributes D on A.Issue = D.Issue and D.IssueAttributeName = 'customfield_12909'
where A.project = 'OSESD'
and A.status not in ('Odrzucone','Anulowane','Anulowano','Odwołane')
and A.Created >= '2022-01-01'
and A.issuetype in ('Incydent')

Main table: dbo.dimIssue A do not have ObjectKey, but I have it thanks to subquery,(select [dbo].[fGetIdObjectV2](IssueAttributeValue) from dimIssueAttributes ds1 where A.Issue = ds1.Issue and IssueAttributeName = 'customfield_11404') as Object_Key_Usługa

Now, how to get information from another table: [dbo].[dimObjectAtributes] to receive for instance ObjectAtributeValue from "service name" based on ObjectKey? Below example from this table:

[dbo].[fGetIdObjectV2] is a function created by my colleague

My goal: I need for every Issue with ObjectKey, ObjectAtributeValue from [dbo].[dimObjectAtributes] where ObjectAtributeName = 'service name'