sql server - Select from multiple tables without cartesian product -


i'm trying create command copy details of client memberships 1 database identical structure. i've pared down bare essentials purposes of question 4 items copy expiry date, subscription id, client id , item id (which service comprises subscription). clients have common guid in both bases. subscription id unique long int should same in both bases , expiry date date. far, easy. tricky part item_id not same in each database. need map 1 the other statement, know how do.

my problem need select destination database's own item table (item_0) in order , insert correct item_id , when thousands of duplicate rows returned. assume need use join avoid have nothing meaningful join item_0 can't results.

insert destdb..subscription (expiry_date,id,client_id,item_id) select      sub_1.expiry_date,     sub_1.id,     cli_0.id client_id,     item_0.id item_id, sourcedb..subscription sub_1,     destdb..item item_0,     destdb..client cli_0 inner join sourcedb..client cli_1    on cli_1.[guid] = cli_0.[guid]  sub_1.id not in (select id destdb..subscription)   , item_0.id =       (select id destdb..collectiondetails               service_id =               (select id destdb..service s_0 s_0.code =                   (select code sourcedb..service s_1 s_1.id =                      (select service_id source..collectiondetails item_1         item_1.id = sub_1.item_id)))              , collection_id =                (select id destdb..collection col_0                   col_0.code =                      (select code sourcedb..collection col_1 col_1.id =                        (select collection_id sourcedb..collectiondetails item_1 item_1.id = sub_1.collection_id)))       )

i afraid updated question more confusing. select in where clause guaranteed return 1 record or should in instead of =?

if there no rule identify particular destdb..item list of matching top 1 should well. still seem item_0 can omitted altogether:

insert destdb..subscription (expiry_date,id,client_id,item_id) select      sub_1.expiry_date,     sub_1.id,     cli_0.id client_id,     (select top 1 id destdb..collectiondetails   --<- limit top 1              service_id =               (select id destdb..service s_0 s_0.code =                   (select code sourcedb..service s_1 s_1.id =                      (select service_id sourcedb..collectiondetails item_1 item_1.id = sub_1.item_id)))              , collection_id =                (select id destdb..collection col_0                   col_0.code =                      (select code sourcedb..collection col_1 col_1.id =                        (select collection_id sourcedb..collectiondetails item_1 item_1.id = sub_1.collection_id)))      ) item_id, sourcedb..subscription sub_1,     destdb..client cli_0 inner join sourcedb..client cli_1    on cli_1.[guid] = cli_0.[guid]  sub_1.id not in (select id destdb..subscription)

please note: if destdb..item empty question example statement not insert anything, answer 1 - item_id set null.

i try splitting task 2 separate statements in 1 transaction:

  1. insert target table null item_id.
  2. update target table new item_id item_id null.
  3. (optional) delete unwanted records appropriate item_id not found.

Comments

Post a Comment

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

i have html progress meter: <div class="col-md-3" style="margin-left: -20px;"> <div class="progress-pos active" id="progess-1"> <div class="progress-pos-inner"> login </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-2"> <div class="progress-pos-inner"> scan items </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-3"> <div class="progress-pos-inner"> confirm address </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-4"> <div class="progress-pos-in...
Read more

caching - How to check if a url path exists in the service worker cache -

i need check if particular url path exists in service worker cache. example, suppose url is: /myserviceworker/service?a=110&b=70 this url exists in cache, there many of them different values of , b. now, suppose want refresh of these urls, how can that? i want know how access key values service worker cache. if know key, plan follows: var url = new url(key); if(url.pathname === "\/myserviceworker/service") refetch key but not sure how cache key , in format is. mean, string or url? cache api has match() method returns promise resolving in response object if match or undefined if no match exists. second parameter object can specify ignoresearch not take account url parameters. the ignoresearch option supported firefox ( chrome status here ). in other hand, retrieve cache entries, can use keys() method.
Read more

Redirect to a HTTPS version using .htaccess -

i need modify .htaccess file redirect urls https version without "www". http://example.com --> https://example.com http://www.example.com --> https://example.com https://www.example.com --> https://example.com this how .htaccess file looks like: rewriteengine on rewritecond %{request_filename} !-d rewritecond %{request_filename} !-f rewriterule ^(.*)$ redirect.php?id=$1&page=$2 [qsa,l] what modifications need make .htaccess in order forward https without "www"? it depends on doing current code. looks file called redirect trying display php page seo friendly url. think might confusing people. anyway, need , force https without www, need 1 more rule above. also second rule needs two capture groups in rewriterule test string because wanting 2 different values using $1 , $2 references. rewriteengine on #rediect www non www and/or http https --- combinations. rewritecond %{http_host} !^example\.com [nc,or] rewritecond %{https} !^...
Read more