v_site_support_orig

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

site_id

INT

10

0

district_id

INT

10

0

market_id

INT

10

√

0

area_id

INT

10

√

0

region_id

INT

10

√

0

user_id

INT

10

0

role_id

INT

10

0

Level

VARCHAR

15

√

null

Relationships

Close relationships within degrees of separation

One

View Definition

select `tb2`.`site`.`id` AS `site_id`,`district`.`org_id` AS `district_id`,`market`.`org_id` AS `market_id`,`area`.`org_id` AS `area_id`,`region`.`org_id` AS `region_id`,`tb2`.`sys_users`.`user_id` AS `user_id`,`tb2`.`sys_roles`.`role_id` AS `role_id`,(case when (`ss`.`user_id` is not null) then 'Site' when ((`distc`.`user_id` is not null) or (`distcb`.`user_id` is not null)) then 'District' when ((`marketc`.`user_id` is not null) or (`marketcb`.`user_id` is not null)) then 'Market' when ((`areac`.`user_id` is not null) or (`areacb`.`user_id` is not null)) then 'Area' when ((`regionc`.`user_id` is not null) or (`regioncb`.`user_id` is not null)) then 'Region' when ((`localec`.`user_id` is not null) or (`localecb`.`user_id` is not null)) then 'Locale' when (`chs`.`user_id` is not null) then 'Contract Holder' else NULL end) AS `Level` from ((((((((((((((((((((`tb2`.`site` join `tb2`.`sys_roles` on((`tb2`.`sys_roles`.`role_type_id` = 1))) join `tb2`.`site_channel` on((`tb2`.`site_channel`.`channel_code` = `tb2`.`site`.`channel_code`))) join `tb2`.`site_org` `district` on((`district`.`org_id` = `tb2`.`site`.`org_id`))) left join `tb2`.`site_org` `market` on((`market`.`org_id` = `district`.`parent_id`))) left join `tb2`.`site_org` `area` on((`area`.`org_id` = `market`.`parent_id`))) left join `tb2`.`site_org` `region` on((`region`.`org_id` = `area`.`parent_id`))) left join `tb2`.`locale` on(((`tb2`.`locale`.`state` = `tb2`.`site`.`state`) and (`tb2`.`locale`.`county` = `tb2`.`site`.`county`)))) left join `tb2`.`site_support` `ss` on(((`ss`.`site_id` = `tb2`.`site`.`id`) and (`ss`.`role_id` = `tb2`.`sys_roles`.`role_id`)))) left join `tb2`.`contract_holder_support` `chs` on(((`chs`.`contract_holder_id` = `tb2`.`site`.`contract_holder_id`) and (`chs`.`role_id` = `tb2`.`sys_roles`.`role_id`)))) left join `tb2`.`locale_contact` `localec` on(((`localec`.`locale_id` = `tb2`.`locale`.`id`) and (`localec`.`role_id` = `tb2`.`sys_roles`.`role_id`) and (`localec`.`channel_id` = `tb2`.`site_channel`.`contact_group`)))) left join `tb2`.`locale_contact` `distc` on(((`distc`.`org_id` = `district`.`org_id`) and (`distc`.`role_id` = `tb2`.`sys_roles`.`role_id`) and (`distc`.`channel_id` = `tb2`.`site_channel`.`contact_group`)))) left join `tb2`.`locale_contact` `marketc` on(((`market`.`org_id` is not null) and (`marketc`.`org_id` = `market`.`org_id`) and (`marketc`.`role_id` = `tb2`.`sys_roles`.`role_id`) and (`marketc`.`channel_id` = `tb2`.`site_channel`.`contact_group`)))) left join `tb2`.`locale_contact` `areac` on(((`area`.`org_id` is not null) and (`marketc`.`org_id` = `area`.`org_id`) and (`marketc`.`role_id` = `tb2`.`sys_roles`.`role_id`) and (`marketc`.`channel_id` = `tb2`.`site_channel`.`contact_group`)))) left join `tb2`.`locale_contact` `regionc` on(((`region`.`org_id` is not null) and (`regionc`.`org_id` = `region`.`org_id`) and (`regionc`.`role_id` = `tb2`.`sys_roles`.`role_id`) and (`regionc`.`channel_id` = `tb2`.`site_channel`.`contact_group`)))) left join `tb2`.`locale_contact` `localecb` on(((`localecb`.`locale_id` = `tb2`.`locale`.`id`) and (`localecb`.`role_id` = `tb2`.`sys_roles`.`role_id`) and (`localecb`.`channel_id` is null)))) left join `tb2`.`locale_contact` `distcb` on(((`distcb`.`org_id` = `district`.`org_id`) and (`distcb`.`role_id` = `tb2`.`sys_roles`.`role_id`) and (`distcb`.`channel_id` is null)))) left join `tb2`.`locale_contact` `marketcb` on(((`market`.`org_id` is not null) and (`marketcb`.`org_id` = `market`.`org_id`) and (`marketcb`.`role_id` = `tb2`.`sys_roles`.`role_id`) and (`marketcb`.`channel_id` is null)))) left join `tb2`.`locale_contact` `areacb` on(((`area`.`org_id` is not null) and (`areacb`.`org_id` = `area`.`org_id`) and (`areacb`.`role_id` = `tb2`.`sys_roles`.`role_id`) and (`areacb`.`channel_id` is null)))) left join `tb2`.`locale_contact` `regioncb` on(((`region`.`org_id` is not null) and (`regioncb`.`org_id` = `region`.`org_id`) and (`regioncb`.`role_id` = `tb2`.`sys_roles`.`role_id`) and (`regioncb`.`channel_id` is null)))) join `tb2`.`sys_users` on((`tb2`.`sys_users`.`user_id` = ifnull(`ss`.`user_id`,ifnull(`distc`.`user_id`,ifnull(`distcb`.`user_id`,ifnull(`marketc`.`user_id`,ifnull(`marketcb`.`user_id`,ifnull(`areac`.`user_id`,ifnull(`areacb`.`user_id`,ifnull(`regionc`.`user_id`,ifnull(`regioncb`.`user_id`,ifnull(`localec`.`user_id`,ifnull(`localecb`.`user_id`,ifnull(`chs`.`user_id`,NULL)))))))))))))))

v_site_support_orig

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views