MySQL: Join distinct rows of two tables in a certain order? -


i have list of inventory units , sale transactions want to, (1) join unit sku, , (2) associate 1 transaction 1 inventory unit in first-in-first-out order date. i'm having trouble second part.

the best can come is:

select `units`.`unit_date`, `units`.`unit_id`, `trans`.`tran_date`, `trans`.`tran_id`, `units`.`unit_sku` `units`     inner join `trans`         on `trans`.`unit_sku` = `units`.`unit_sku` group `trans`.`tran_id`, `trans`.`unit_sku` order `units`.`unit_date` asc, `trans`.`tran_date` asc ;

units table:

unit_date  | unit_id | unit_sku 2015-06-01 | 1       | u1klm 2015-06-02 | 2       | u1klm 2015-06-03 | 3       | u2qrs 2015-06-04 | 4       | u2qrs 2015-06-05 | 5       | u1klm

trans table:

tran_date  | tran_id | unit_sku 2015-06-11 |       | u2qrs 2015-06-12 | b       | u1klm 2015-06-13 | c       | u1klm 2015-06-14 | d       | u2qrs 2015-06-15 | e       | u1klm

the desired result 1 tran_id joined 1 unit_id of unit_sku earliest-to-latest order of unit_date:

unit_date  | unit_id | tran_date  | tran_id | unit_sku 2015-06-01 | 1       | 2015-06-12 | b       | u1klm 2015-06-02 | 2       | 2015-06-13 | c       | u1klm 2015-06-03 | 3       | 2015-06-11 |       | u2qrs 2015-06-04 | 4       | 2015-06-14 | d       | u2qrs 2015-06-05 | 5       | 2015-06-15 | e       | u1klm

the query (undesired) result joins tran_id unit_id of earliest occurrence of unit_sku:

unit_date  | unit_id | tran_date  | tran_id | unit_sku 2015-06-01 | 1       | 2015-06-12 | b       | u1klm 2015-06-01 | 1       | 2015-06-13 | c       | u1klm 2015-06-01 | 1       | 2015-06-15 | e       | u1klm 2015-06-03 | 3       | 2015-06-11 |       | u2qrs 2015-06-03 | 3       | 2015-06-14 | d       | u2qrs

any ideas on how desired result? in setup, unit_date , tran_date sortable; rest randomly generated.

repro script:

drop temporary table if exists `units`; drop temporary table if exists `trans`; create temporary table `units` (`unit_date` date, `unit_id` char(1) , `unit_sku` char(5), primary key(`unit_id`)); create temporary table `trans` (`tran_date` date, `tran_id` char(1) , `unit_sku` char(5), primary key(`tran_id`));  insert `units` (`unit_date`, `unit_id`, `unit_sku`) values     ('2015-06-01', '1', 'u1klm')     , ('2015-06-02', '2', 'u1klm')     , ('2015-06-03', '3', 'u2qrs')     , ('2015-06-04', '4', 'u2qrs')     , ('2015-06-05', '5', 'u1klm') ;  insert `trans` (`tran_date`, `tran_id`, `unit_sku`) values     ('2015-06-11', 'a', 'u2qrs')     , ('2015-06-12', 'b', 'u1klm')     , ('2015-06-13', 'c', 'u1klm')     , ('2015-06-14', 'd', 'u2qrs')     , ('2015-06-15', 'e', 'u1klm') ;  select `units`.`unit_date`, `units`.`unit_id`, `trans`.`tran_date`, `trans`.`tran_id`, `units`.`unit_sku` `units`     inner join `trans`         on `trans`.`unit_sku` = `units`.`unit_sku` group `trans`.`tran_id`, `trans`.`unit_sku` order `units`.`unit_date` asc, `trans`.`tran_date` asc ;

i believe you're looking for: (this assuming 1 1 relationship)

set @unitrn := 0; set @transrn :=0; select a.`unit_date`, a.`unit_id`, b.`tran_date`, b.`tran_id`, a.`unit_sku` (select @unitrn := @unitrn + 1 rownum, unit_date, unit_id, unit_sku units order unit_sku, unit_date asc) join (select @transrn := @transrn + 1 rownum, tran_date, tran_id, unit_sku trans order unit_sku, tran_date asc) b on a.unit_sku = b.unit_sku , a.rownum = b.rownum order a.unit_date asc

Comments

Post a Comment

Popular posts from this blog

c# - Validate object ID from GET to POST -

i have method allows user access comment have made on site. have post method lets them update comment. my normal solution send entire comment model through, let them update it, update on database when post back. involve sending commentid through in hiddenfor , can manipulated. how can verify commentid sent in get method same i'm getting in post , not able alter comment wish? how can verify commentid sent in method same i'm getting in post? basically have validate following things in post - user logged-in user. authenticated users post comments. commentid in post , should valid commentid , should present in database. userid associated logged-in user should same userid associated comment . comment should contain userid column, can check @ time of update. to make sure update happens comment has been sent in get - hold commentid in session , in post action compare commentid value in session .
Read more

node.js - Custom Model Validator SailsJS -

when using custom model validator checks record within model. return within spread doesn't seem end control flow. //post.js model /** * custom validator **/ types: { isuservalid: function(user_id) { var promise = require('bluebird'); promise.all([ user.findone({id: user_id}) ]) .spread(function(user) { console.log(user); if (user === null || user === undefined) { console.log('failed'); return false; }else{ console.log('passed'); return true; } }); } }, my response standard validation failed response. { "error": "e_validation", "status": 400, "summary": "1 attribute invalid", "model": "post", "invalidattributes": { "owner": [ { "rule": "isuservalid", ...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more