hey everyone, ok i have a table of companies:
db.define_table('companys', Field('company_name', length=128, requires=NE, comment='Legal Name of the Company or Entity.'), etc...) and the auth_user table with the added field: db.define_table( auth.settings.table_user_name, ... Field('company_id', db.companys, requires=IS_EMPTY_OR(IS_IN_DB(db, db.companys.id, '%(company_name)s %(telephone)s %(zip)s')), writable=False, readable=False), etc...) i would like a join that selects the company of the auth.user_id and any company that doesn't have a user associated to it at all, i.e., company_id==None. i thought this was just a simple outer join, like tUser = db.auth_user tCmp = db.companys db().select(tUser.id, tUser.email, tCmp.ALL, orderby=tCmp.company_name, left=tUser.on((tCmp.id==tUser.company_id) & (tUser.id==auth.user.id))) but i get those companies that also have a company_id, it just Nones the tUser fields. so i tried many many variations to try and get the right return, but i am out of ideas. i also found that this returns a different result then the above: db(tCmp.id>0).select(tUser.id, tUser.email, tCmp.ALL, orderby=tCmp.company_name, left=tUser.on((tCmp.id==tUser.company_id) & (tUser.id==auth.user.id))) but why? anyway, if anyone could help me get the right select statement, i would be grateful, thanx in advance. lucas -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.