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]> 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] > > 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
