hierarchy - SQL bubbling or hierarchical pattern for searching higher and higher levels -

we have company, branch, , user tables/levels. each level has nullable id associated configuration in configuration table. if configuration id null, should return configuration @ higher level, , there non nullable id associated higher level. if there no configuration associated @ highest level, should return nothing. 1 additional complication configuration should have isdeleted value of 0.

what's sql pattern such hierachy? did search on web, since i'm not sure call it, didn't yield useful results.

i tried sql fiddle here: http://sqlfiddle.com/#!3/f1815/10/0 , don't pattern i'm using. schema bunch of tables each 1 element, , company 1 points config. schema:

-- ids uniqueidentifiers -- pks , fks primary , foreign keys be. create table all_user (   id int not null, -- pk   branchid int not null, -- fk   configid int -- fk ); create table branch (   id int not null, -- pk   companyid int not null, -- fk   configid int -- fk ); create table company (   id int not null, -- pk   configid int -- fk );  create table config (   id int not null, -- pk   name varchar(36) not null,   isdeleted bit default(0) );  insert config (id, name) values (1, 'firstconfig'); insert company(id,configid) values (1,1); insert branch(id, companyid) values (1, 1); insert all_user(id, branchid) values(1, 1); 

and here query:

    declare @userid int select @userid = 1  declare @uconfigid int declare @bconfigid int declare @cconfigid int declare @numconfigs int     select @numconfigs = 0  -- update (makes query work)  select @uconfigid = au.configid, @bconfigid = b.configid, @cconfigid = c.configid all_user au  join branch b on au.branchid = b.id join company c on b.companyid = c.id au.id = @userid  if @uconfigid not null begin     select @numconfigs = count(*)      config     id = @uconfigid     , isdeleted = 0      if @numconfigs = 1     begin       select *       config       id = @uconfigid       , isdeleted = 0     end end  if @numconfigs = 0 , @bconfigid not null begin     select @numconfigs = count(*)      config     id = @bconfigid     , isdeleted = 0      if @numconfigs = 1     begin       select *        config       id = @bconfigid       , isdeleted = 0     end end  if @numconfigs = 0 , @cconfigid not null begin     select @numconfigs = count(*)      config     id = @cconfigid     , isdeleted = 0      if @numconfigs = 1     begin       select *       config       id = @cconfigid       , isdeleted = 0     end end 

also, don't i'm using joins, since want shortcircuit @ first level returns valid (nondeleted) configuration.

is there better approach? perhaps recursive?

thanks.