Salient details from two tables: db.define_table( 'suppliers', Field('name', length=256, required=True, notnull=True), ....
db.define_table( 'supplier_contacts', Field('supplier_id', db.suppliers), Field('first_name', length=32, required=True, notnull=True), The code below worked in 1.99.7. If the supplier had two contacts, it would return two rows as expected, both with the same supplier data but each with individual contact data. In 2.2.1 it returns no rows. def get_approved_suppliers(product_id): return db( (db.product_suppliers.product_id==product_id) & (db.product_suppliers.supplier_id==db.suppliers.id) ).select( db.suppliers.id, db.suppliers.name, # more supplier details omitted for brevity, db.supplier_contacts.id, db.supplier_contacts.first_name, # contact details omitted for brevity. left = db.supplier_contacts.on( db.supplier_contacts.supplier_id==db.suppliers.id ) ) This is the query as shown by db._lastsql. (Broken into chunks for readability) SELECT suppliers.id, suppliers.name, suppliers.address, suppliers.address_2 , suppliers.city, suppliers.state, suppliers.zip, suppliers.land_line,suppliers .fax, suppliers.email, suppliers.website, supplier_contacts.id,supplier_contacts .first_name, supplier_contacts.middle_name, supplier_contacts.last_name,supplier_contacts .generation, supplier_contacts.email, supplier_contacts.mobile,supplier_contacts .land_line, supplier_contacts.fax FROM product_suppliers, suppliers LEFT JOIN supplier_contacts ON (supplier_contacts.supplier_id = suppliers.id ) WHERE (((((product_suppliers.product_id = 340) AND (product_suppliers.supplier_id = suppliers.id)) AND (product_suppliers.is_active = 'T')) AND (suppliers.is_active = 'T')) AND (supplier_contacts.is_active = 'T')); --