Get table Data from single table but from different paths

union

A greet to all Experts here at StackExchange.

The problem I have faced seems to be a simple one, it's just my low knowledge of SQL that unables me from doing it.

I have a Table called 'ApplicationPlans' with data about plans in each application of mine.
This table is connedcted to the 'Applications' table with ApplicationId as foreign key.
The 'Applications' is connected to the 'UserRoles' table once directly and once through the 'Clients' table. Allowing us to set access levels on the application level and/or client access level.

The figure can be seen here

My goal is to fetch data from 'ApplicationPlans' table for a user according to the roles he has.
Since this data is going to be in a grid, it's better that I dont use a UNION of two select queries like image here

of course that is my idea, you might say it works with same performance and I will do it.

So that was my situation,
Any suggestions is appreciated

  • For sake of clearification I must add,
    I need the those data from 'Applicationplans' which my user have access to. This is determined by first finding roles for this user and then in two phases:

    1. Find clients that this user have access to, then find their applications and then find their ApplicationPlans.
    2. Find Applications that this user have direct access to, then find ApplicationPlans that they have.

The result intended to be a union of these two sets together.

Best Answer

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.

Related Question