Hi everyone

I would if anyone could give me some pointers as struggling a bit. My SQL query knowledge is not great. I need to tranlate this into DAX. and doing my research but goig around in circles a bit!!

the SQL query is:

select personview.forename, personview.surname,
wlocation.dateanswervalue as "Expiry Date from Last Carer Monitoring",
personOrgRelationshipView.organisationname as "Scheme",

publia.answervalue as "Expiry Date from Pre-Placement Checklist",

personview.personid
from personview

left join formanswerpersonview wlocation
on personview.personid=wlocation.answerforsubjectid
and designguid ='f98ab2f7-7010-4c93-b9de-2801066ccda8'
And controlname='certificateExpiryDate'
and instancestate='COMPLETE'
and wlocation.instanceid= (
select max (x.instanceid)
from formanswerpersonview x
where x.answerforsubjectid = wlocation.answerforsubjectid
and x.designguid = wlocation.designguid
and x.instancestate='COMPLETE'
)

left join formanswerpersonview publia
on personview.personid=publia.answerforsubjectid
and publia.designguid ='3f6d7b80-e1b6-4286-b6a9-803fc569706b'
And publia.controlname='date_21'
and publia.instancestate='COMPLETE'
and publia.instanceid= (
select max (x.instanceid)
from formanswerpersonview x
where x.answerforsubjectid = publia.answerforsubjectid
and x.designguid = publia.designguid
and x.instancestate='COMPLETE'
)
INNER join personOrgRelationshipView
on personview.personid=personOrgRelationshipView.personid

inner join classificationPersonView places
on places.personid=personview.personid
wHERE code='APP'
And status='ACTIVE'
AND endDate IS NULL

order by forename

Clearly the inner joins are fine but it is the left join i am unclear on? any help would be greatly appriciated, as i continue to research

Many thanks

Tim

Forum Discussion

SQL left Join

Left Join

Join the Community!

left join in Report

Issue with left join using measures

Left Outer Join through Relation ship joining