Hi, Gaurav, Your idea makes sense. You might have to implement some of the step yourself if you plan to use FastBit as the basis.
Good luck. John On 7/20/12 12:43 AM, Gaurav Agarwal wrote: > Hi John, > > Thanks for the suggestion. I thought a bit more about this and have an > alternate approach. An ideal scenario would be that this 'new' join > operation returns back ibis::table so that it can be chained with > other operations. > > Pls see if it makes sense. The only point where I am have problems is > how to make the number of columns same in T3 and T4 prior to merge > operation (T3 contains selection from both tables whereas T4 contains > selection from T1 only). Rows in T4 need to filled with null for > columns corresponding to T2. > > LeftJoin: (T1, T2 on K1) Select : (T1.a Ta.b, T2.c, T2.d, T2.e) > - Get All distinct K1 from T1 -> D1 > - Natural Join T1, T2 (on K1) -> T3 > - Get All distinct K1 from T3 ->D2 > - D = D1-D2 > - Select all rows from T1 where K1 IN D (fill the selected T2 columns > with null) -> T4 > - Return T3.merge(T4) > > cheers, > gaurav > > On Fri, Jul 20, 2012 at 5:07 AM, K. John Wu <[email protected] > <mailto:[email protected]>> wrote: > > Hi, Gaurav, > > Thanks for your interest in FastBit. We always welcome questions. > > Regarding outer join, FastBit does not support it directly. One way > to implement it would be to break up the join and issue a query on > each distinct value in the left table. In your example, do the > following > > - issue a query to get all distinct value of of a.first_col and > a.second_col (subject to whatever other conditions desired) > - for each combination of a.first_col and a.second_col, issue the > query on table b. > > Hope this helps. > > John > > > > On 7/19/12 9:40 AM, Gaurav Agarwal wrote: > > Hi John, > > > > Firstly, apologies for troubling you this frequently.. > > I was curious how could I achieve LEFT OUTER JOIN using FastBit; for > > instance if I have following two tables 'a' and 'b' and I do Join a > > and b on first column (a.first_col=b.first_col) and then select > > b.second_col, sum(a.second_col), I get USA-500, UK-100, NULL-500 as > > result? > > > > Is it directly possible using existing fastbit? If not what would be > > the best way to implement this? > > > > cheers, > > gaurav > > > > --------- > > a > > --------- > > 1,200 > > 1,300 > > 2,100 > > 3,400 > > 4,100 > > --------- > > > > --------- > > b > > --------- > > 1,USA > > 2,UK > > --------- > > > > > > > > > > _______________________________________________ > > FastBit-users mailing list > > [email protected] <mailto:[email protected]> > > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users > > > > _______________________________________________ FastBit-users mailing list [email protected] https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
