Get table Data from single table but from different paths
Not sure I understand what the problem is, but you can use a derived table like:

select ur.id, c.scopeid
from userroles ur
left join clients c
    on ur.scopeid = c.id


and then join that with the rest of the tables. Something like:

select ...
from (
    select ur.id, c.id as clientid, ...
    from userroles ur
    left join clients c
        on ur.scopeid = c.id
) dt
join applications a
    on dt.id = a.id
    or dt.clientid = a.clientid
join applicationplans ap
    on ap.applicationid = a.id


In general DDL (create table ..., etc) is preferred over images for describing tables.