I will make a guess:
b = db.tblB.with_alias('b')
db(db.tblB.fkA ==
1).select(db.tblB.name,b.data,left=b.on((b.fkA==db.tblA.fkA)&(db.tblA.id==1)))
If you show your SQL we can make this right. You have not explained
why the output is
100,'aaa','xxx'
101,'bbb','yyy'
102,'ccc',None
and not
100,'aaa','yyy'
101,'bbb','xxx'
102,'ccc',None
you have not explained the role of tblA.
I strongly suspect there is a better way to do this anyway.
On Sep 3, 4:36 pm, Noel Villamor <noe...@gmail.com> wrote:
> TABLE db.tblB
> fkA, name, data
> 1,100,'aaa'
> 1,101,'bbb'
> 1,102,'ccc'
> 2,100,'xxx'
> 2,101,'yyy'
>
> 1st query: db(db.tblB.fkA == 1).select(db.tblB.name,db.tblB.data)
> name,data
> 100,'aaa'
> 101,'bbb'
> 102,'ccc'
>
> 2nd query: db(db.tblB.fkA == 2).select(db.tblB.name,db.tblB.data)
> name,data
> 100,'xxx'
> 101,'yyy'
>
> Desired left join result:
> 100,'aaa','xxx'
> 101,'bbb','yyy'
> 102,'ccc',None
>
> I could do this in SQL but I do not know how to use aliases in web2py.
> Thank you for the replies Massimo and pbreit.
