sql - Left Join table giving uncessary extra rows -

-

good day.

i writing query against 2 tables, table , table b.

table has 57 rows. resultant should same row count table a.

table b has 44 rows.

script

;with ss       (select distinct *             tablea),       ss2       (select distinct *             tableb)  select distinct a.lotid, a.name,                 b.machine_id    ss         left join ss2 b                on b.recipe_id = a.id  b.machine_id = 1

table a

           id   lotid   name             62  fr 3    2000w - 200w/ms              63  fr 3    2000w - 180w/ms              85  fr 2    2000w - 173w/ms              87  fr 2    1200w - 173w/ms              197 fr 2    1100w - 185w/ms              200 fr 2    2000w - 185w/ms              204 fr 2    1200w - 41w/ms               218 fr 5    kst / test               220 fr 5    2000w - 29w/ms               221 fr 4    2000w - 185w/ms              222 fr 5    2000w - 185w/ms              223 fr 2    n2000w - 200w/ms                 224 fr 3    2000w - 185w/ms              225 fr 5    2000w - 257w/ms              226 fr 4    2000w - 257w/ms              227 fr 5    1300w - 190w/ms              228 fr 4    1300w - 190w/ms              229 fr 4    2000w - 200w/ms              231 fr 2    2000w - 152w/ms              233 fr 3    2000w - 152w/ms              234 fr 5    600w - 29w/ms                235 fr 4    600w - 29w/ms                236 fr 5    2000w - 200w/ms              238 fr 5    1500w - 41w/ms               239 fr 4    1500w - 41w/ms               240 fr 3    1100w - 218w/ms              241 fr 2    1100w - 218w/ms              242 fr 4    1100w - 218w/ms              243 fr 5    1100w - 218w/ms              244 fr 5    1100w - 175w/ms              245 fr 5    2000w -173w/ms               246 fr 4    2000w - 173w/ms              247 fr 2    1300w - 190w/ms              248 fr 3    1300w - 190w/ms              249 fr 5    2000w - 207w/ms              250 fr 4    2000w - 207w/ms              251 fr 5    2000w - 182w/ms              252 fr 5    2000w - 190w/ms              253 fr 5    2000w - 186w/ms              254 fr 2    dm2000w-200w                 255 fr 2    2000w - 200w/ms              256 fr 3    n2000w -200w/ms              257 fr 5    1300w - 177w/ms              258 fr 4    n2000w - 207w                259 fr 2    2000w - 202w/ms              260 fr 3    2000w - 202w/ms              261 fr 3    1300w - 218w/ms              262 fr 2    1300w - 218w/ms              263 fr 2    1300w - 155w/ms              264 fr 3    1300w - 155w/ms              265 fr 5    2000w - 43w/ms               266 fr 4    2000w - 43w/ms               267 fr 2    2000w - 257w/ms              268 fr 3    2000w - 257w/ms              269 fr 5    2000w - 178w/ms              270 fr 5    1300w - 155w/ms              271 fr 4    1300w - 155w/ms

table b has following data

  machine_id   recipe_id     1   63     1   85     1   87     1   197     1   200     1   223     1   231     1   241     1   247     1   254     1   255     1   259     1   262     1   263     1   267     2   62     2   85     2   87     2   197     2   200     2   224     2   248     2   256     2   260     2   261     2   264     2   268     3   218     3   226     3   228     3   229     3   246     3   250     3   258     3   266     3   271     4   218     4   225     4   227     4   236     4   243     4   257     4   265     4   269     4   270

for result, trying 57 rows . results getting are

id   lotid   name          machine_id 63  fr 3    2000w - 180w/ms 1 85  fr 2    2000w - 173w/ms 1 87  fr 2    1200w - 173w/ms 1 197 fr 2    1100w - 185w/ms 1 200 fr 2    2000w - 185w/ms 1 223 fr 2    n2000w - 200w/ms    1 231 fr 2    2000w - 152w/ms 1 241 fr 2    1100w - 218w/ms 1 247 fr 2    1300w - 190w/ms 1 254 fr 2    dm2000w-200w    1 255 fr 2    2000w - 200w/ms 1 259 fr 2    2000w - 202w/ms 1 262 fr 2    1300w - 218w/ms 1 263 fr 2    1300w - 155w/ms 1 267 fr 2    2000w - 257w/ms 1

as can see getting 15 rows or so. want getting 57. how return rows table while joining table b?

any appreciated. thanks.

remove this

where b.machine_id = 1

only use table 'a' in if dont want table b contraining resulst

for criteria relevan table 'b', put in join.

e.g.

;with ss   (select distinct *         tablea),   ss2   (select distinct *         tableb)  select distinct a.lotid, a.name,             b.machine_id    ss         left join ss2 b                on b.recipe_id = a.id , b.machine_id = 1 a.name = 'foo'

Comments

Post a Comment

Popular posts from this blog

html - Styling progress bar with inline style -

-
i trying set progress bar color red using inline style. need in way because have access <body> part of document. style need apply: progress { color: red } progress::-moz-progress-bar { background: red;} progress::-webkit-progress-value { background: red; } progress[aria-valuenow]:before { background: red; } i tried <progress style="background: red;"> incorrect effect, progress bar color turns blue red background. guide right direction desired style? thank in advance. here live example: https://jsfiddle.net/z36d3f6l/
Read more

java - Oracle Sql developer error: could not install some modules -

-
i have downloaded fresh copy of sqldeveloper ( sqldeveloper-4.1.3.20.78-no-jre ) oracle website. when unzip , lauch gui, keep getting following error message: warning - not install modules: dynamic module config - no module providing capability org.netbeans.netbinox found. my system: windows 7 32 bit hp probook. jdk 1.6 , 1.7, 1.8 available in java/jdk subdirectory. netbeans 8.0.2 installed finally got way out after dirty hours. extracted sqldeveloper zip in nested directory. i.e: e://a/b/c/sqldeveloper. starting sqldeveloper gui above directory threw erros. solution: go in c:\users**username**\appdata\roaming : delete sql developer , sqldeveloper folders extract sql developer zip in new non nested directory. i.e: e:// start , should work now reference: https://community.oracle.com/thread/3870680?start=0&tstart=0
Read more

How to use autoclose brackets in Jupyter notebook? -

-
this may sound silly question, how make use of autoclose brackets in jupyter notebook? example, when type print( jupyter notebook auto-closes brakets print() and places cursor inside. type argument, say print(1 + 1) now cursor between second 1 , right bracket ) . key navigate right of ) ? of course can press end or → or ) achieve this, not seem save time, purpose of brackets autoclose suppose? thanks in advance.
Read more