Thanks again Raul and Ric. I wrote up a wiki article that shows the joins in action: http://www.jsoftware.com/jwiki/JoeBogner/Joins
I'm note quite happy with the report queries at the end... I need to clean those up still On Tue, Aug 12, 2014 at 8:19 PM, Raul Miller <[email protected]> wrote: > Oops, not exactly, but yes - good point. > > Neither of the expressions I gave matches "left outer join" semantics > for the general case. > > (Not that "left outer join" is always the right semantics, either, but > I should have been more careful, at least when I went to the effort of > building up a complex statement.) > > Thanks, > > -- > Raul > > > On Tue, Aug 12, 2014 at 7:49 PM, Ric Sherlock <[email protected]> wrote: >> I think the answers so far will give you the outer join rather than a left >> join (which gives the same result for the example you gave). >> If you do need a left join then you will need to constrain b to only the >> items that also exist in a as well. Here is a solution for that case. >> >> I've given a new b (b2) that shows the differences between join types. >> >> joinLeft=: [ , ] #~ e.~&:({."1) >> summary=: (~.@:({."1) ,. {."1 ]/. {:"1) >> >> a=: _2]\'a';1;'b';2;'c';3 >> b=: _2]\'c';5;'a';4 >> >> b2=: _2]\'c';5;'a';4;'d';6;'e';7 >> >> a summary@joinLeft b2 >> >> ┌─┬─┬─┐ >> >> │a│1│4│ >> >> ├─┼─┼─┤ >> >> │b│2│ │ >> >> ├─┼─┼─┤ >> >> │c│3│5│ >> >> └─┴─┴─┘ >> >> >> Here are solutions for the other join types: >> joinRight=: ] , [ #~ e.&:({."1) >> >> joinInner=: (, ([ #~ {."1@[ e. ]) ([ -. -.)&:({."1)) >> >> joinOuter=: , >> >> >> >> >> >> >> On Wed, Aug 13, 2014 at 10:54 AM, Raul Miller <[email protected]> wrote: >> >>> That (using /.) is what I was going to say. >>> >>> >>> S >>> >>> p >>> >>> o >>> >>> i >>> >>> l >>> >>> e >>> >>> r >>> >>> >>> >>> S >>> >>> p >>> >>> a >>> >>> c >>> >>> e >>> >>> . >>> >>> . >>> >>> . >>> >>> ({."1 ]/. {:"1) a,b >>> >>> Thanks, >>> >>> -- >>> Raul >>> >>> On Tue, Aug 12, 2014 at 6:36 PM, robert therriault >>> <[email protected]> wrote: >>> > Hi Joe, >>> > >>> > Henry touches on this in JforC: Apply On Subsets: Dyad u/. >>> > >>> > >>> http://www.jsoftware.com/help/jforc/loopless_code_v_partitions.htm#_Toc191734451 >>> > >>> > He does a total instead of an append, but I think you'll get the idea. >>> > >>> > Cheers, bob >>> > >>> > On Aug 12, 2014, at 3:19 PM, Joe Bogner <[email protected]> wrote: >>> > >>> >> I've been fiddling with this for 30 minutes and I can't figure it >>> >> out... Maybe it's too late in the day >>> >> >>> >> a=:_2]\'a';1;'b';2;'c';3 >>> >> b=:_2]\'c';5;'a';4 >>> >> >>> >> what verb will produce this? >>> >> >>> >> ]a,.(4;'';5) >>> >> ┌─┬─┬─┐ >>> >> │a│1│4│ >>> >> ├─┼─┼─┤ >>> >> │b│2│ │ >>> >> ├─┼─┼─┤ >>> >> │c│3│5│ >>> >> └─┴─┴─┘ >>> >> >>> >> >>> >> I went down this path: >>> >> >>> >> ] ((0{"1 b)i.(0{"1 a)) >>> >> 1 2 0 >>> >> >>> >> I figured I could use { and return '' if the index was greater than >>> >> the length (e.g., 2 should be '') >>> >> >>> >> I tried using :: to return '' on index error but I couldn't get that to >>> work >>> >> >>> >> NB. not what I wanted >>> >> ] ((0{"1 b)i.(0{"1 a)) ({ :: ('no'"_)) b >>> >> no >>> >> >>> >> Then I tried using ^: to return '' if the length was greater than # b >>> >> >>> >> Then I tried writing a explicit verb using if. >>> >> >>> >> Then I threw in the towel and decided to ask for help since this must >>> >> be a common need >>> >> >>> >> I'm sure I've solved this before but cannot remember how >>> >> ---------------------------------------------------------------------- >>> >> For information about J forums see http://www.jsoftware.com/forums.htm >>> > >>> > ---------------------------------------------------------------------- >>> > For information about J forums see http://www.jsoftware.com/forums.htm >>> ---------------------------------------------------------------------- >>> For information about J forums see http://www.jsoftware.com/forums.htm >>> >> ---------------------------------------------------------------------- >> For information about J forums see http://www.jsoftware.com/forums.htm > ---------------------------------------------------------------------- > For information about J forums see http://www.jsoftware.com/forums.htm ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
