sql - My INNER JOIN is filtering out too much -

-

my inner join filtering out or can't see difference is.

i trying consolidate 2 tables 2 different databases on same server.

my first step find values match:

select intl.*   tbldata intl inner join [db].dbo.tbldata on     intl.dataid = us.dataid ,    intl.anotherid = us.anotherid ,    intl.datavalue = us.datavalue ,    intl.textvalue = us.textvalue ,    intl.defaultvalue = us.defaultvalue ,    intl.ratevalue = us.ratevalue ,    intl.yetanotherid = us.yetanotherid

i've checked columns , same type , same varchar values. query should bring matching values.

however... not case. returning 200+ records, if run query after insert 200+ records temp table:

select intl.dataid   tbldata intl left join tempdatatable tdt on     tdt.dataid = intl.dataid ,    tdt.anotherid = intl.anotherid ,    tdt.datavalue = intl.datavalue ,    tdt.textvalue = intl.textvalue ,    tdt.defaultvalue = intl.defaultvalue ,    tdt.ratevalue = intl.ratevalue ,    tdt.yetanotherid = intl.yetanotherid  tdt.dataid null

this query returns 1500+ rows.

i've run similar query data , found same discrepancy (1500+ rows returned).

i looked @ 1 record see if identify whacky, , found records each table (intl , us) same!

 dataid: 1  anotherid: 1  datavalue: null  textvalue: normal  defaultvalue: 0  ratevalue: null  yetanotherid: 1

i imagine inner join finding difference between 2 tables structurally can not see it.

any ideas on cause this?

this caused null values have in table failing equality checks of left join.

null values aren't equal anything, not null of same datatype.

to remedy this, can add check on clause null values on both sides in addition existing join conditions:

select intl.dataid   tbldata intl left join tempdatatable tdt     on  (             (tdt.dataid null , intl.dataid null)                         or tdt.dataid = intl.dataid         )     , (             (tdt.anotherid null , intl.anotherid null)                   or tdt.anotherid = intl.anotherid         )     , (             (tdt.datavalue null , intl.datavalue null)                   or tdt.datavalue = intl.datavalue         )     , (             (tdt.textvalue null , intl.textvalue null)                   or tdt.textvalue = intl.textvalue         )     , (             (tdt.defaultvalue null , intl.defaultvalue null)              or tdt.defaultvalue = intl.defaultvalue         )     , (             (tdt.ratevalue null , intl.ratevalue null)              or tdt.ratevalue = intl.ratevalue         )     , (             (tdt.yetanotherid null , intl.yetanotherid null)              or tdt.yetanotherid = intl.yetanotherid         )  tdt.dataid null

Comments

Post a Comment

Popular posts from this blog

javascript - Slick Slider width recalculation -

-
i'm trying add slide in panel on slick container css. carousel works except new width not being calculated when space has been added. recalibrate once advance next slide i'd happen each time panel opened or closed. i've tried adding both margin , padding, neither makes difference. ideas? https://jsfiddle.net/mhigley/dpf7mlpl/ html: <button type="button" id="button"><i class="fa fa-arrow-right"></i></button> <aside> <h2>slide in panel</h2> <ol> <li>list item</li> <li>list item</li> <li>list item</li> <li>list item</li> <li>list item</li> </ol> </aside> <main role="main"> <div class="sections"> <section> <article> <h2>first slide</h2> <p>lorem ipsum d...
Read more

jsf - PrimeFaces Datatable - What is f:facet actually doing? -

-
looking through primefaces code in app noticed following line: <f:facet name="header">#{trainsearch.traincount} trains</f:facet> it looks overriding header, makes sense, can explain me in little more detail? what happening line of code? complete code listed below: <p:datatable id = "results" value = "#{trainsearch.trains}" var = "train" rendered="#{not empty trainsearch.trains}" styleclass = "train-search-table horizontal-border"> <f:facet name="header">#{trainsearch.traincount} trains</f:facet> <p:column headertext = "train id"> <p:panelgrid columns="1" styleclass = "train-id-grid" layout = "grid"> <h:outputtext styleclass = "train-id-label" value="#{train.traini}"/> <h:outputtex...
Read more

javascript - Error handling and status codes with knex in koa -

-
i'm experimenting koa , knex build restful web service, , while got basics working i'm stuck @ handling errors. i'm using koa-knex-middleware wraps knex in generator. i have following code defines happens , post requests particular resource: var koa = require('koa'); var koabody = require('koa-body')(); var router = require('koa-router')(); var app = koa(); var knex = require('./koa-knex'); app.use(knex({ client: 'sqlite3', connection: { filename: "./src/server/devdb.sqlite" } })); router .get('/equipment', function *(next){ this.body = yield this.knex('equipment'); }) .post('/equipment', koabody, function *(next){ this.body = yield this.knex('equipment').insert(this.request.body) }); app.use(router.routes()).use(router.allowedmethods()); app.listen(4000); this works in general, can't manage change http status codes. examp...
Read more