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.


Comments

Post a Comment

Popular posts from this blog

python - Subclassed QStyledItemDelegate ignores Stylesheet -

-
i'm trying subclass qstyleditemdelegate remove focus rectangle in qcombobox . even though i'm calling base implementation of paint function , nothing else, result different. looks if parts of style-sheet influence bounding box of item taken consideration. class pstyleditemdelegate(qstyleditemdelegate): def __init__(self, *args, **kwds): super(pstyleditemdelegate, self).__init__(*args, **kwds) def paint(self, *args, **kwargs): qstyleditemdelegate.paint(*args, **kwargs) what have paint unmodified qstyleditemdelegate ? as suggested tried replacing pyside pyqt4 , works, appears bug. update pyside 1.1.2 1.2.1 result same. unfortunately switch breaks other parts of code, if there no other suggestions i'm going accept answer. edit bug tracked here
Read more

java - HttpClient 3.1 Connection pooling vs HttpClient 4.3.2 -

-
i have used httpclient3.1(java) connection pooling below : import org.apache.commons.httpclient.httpclient; import org.apache.commons.httpclient.multithreadedhttpconnectionmanager; import org.apache.commons.httpclient.params.httpclientparams; import org.apache.commons.httpclient.params.httpmethodparams; import org.apache.log4j.logger; public class httpclientmanager { private static logger logger = logger.getlogger(httpclientmanager.class); private static multithreadedhttpconnectionmanager connectionmanager = new multithreadedhttpconnectionmanager(); private static httpclientparams clientparams = new httpclientparams(); static { try { clientparams.makelenient(); clientparams.setauthenticationpreemptive(false); clientparams.setparameter(httpmethodparams.so_timeout, new integer(30000)); // set so_timeout clientparams.setparameter(httpmethodparams.head_body_check_timeout, new integer(3000)); // head responses ...
Read more

node.js - StackOverflow API not returning JSON -

-
i using node.js script gather data stackoverflow. know responses gzipped, put code in should take care of that. script follows: var request = require('request'); var zlib = require('zlib'); function getstackoverflowresponse(url, callback){ request(url, {encoding: null}, function(err, response, body){ if(response.headers['content-encoding'] == 'gzip'){ zlib.gunzip(body, function(err, dezipped) { callback(dezipped); }); } else { callback(body); } }); } var url = "https://api.stackexchange.com/docs/questions#pagesize=2&order=desc&min=2014-01-04&max=2014-02-02&sort=activity&tagged=apigee&filter=default&site=stackoverflow&run=true"; getstackoverflowresponse(url, function(questions) { console.log(questions); }); instead of getting json output, i'm getting following response: buffer 0d 0a 0d 0a 0d 0a 0d 0a 3c 21 44 4f 43 54 59 50 45 20 48...
Read more