SQL query to find all records which are not in another table

[Vladimir Makarov]

I'm just a little bit confusing because I can't make correct selection to find records that are absent in another (referenced) table.
It's clear to me how to make inner join:

rows = db(db.persons).select(join=db.tbl.on(db.persons.id==db.tbl.person), groupby=db.persons.f_name, orderby=~db.persons.id)

But how to acheive the opposite sesult?

How to implement this statement?

SELECT * 
FROM   persons 
WHERE  id NOT IN (SELECT id FROM tbl.person)

Thanks!

[Leonel Câmara]

You can use belongs. You ask for those that do not belong.

http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#belongs

something like

tbl_person =  db()._select(db['tbl.person'].id)

db(~db.persons.id.belongs(tbl_person)).select()

Do you actually have dots in your tablename? That's a terrible idea.

[Vladimir Makarov]

Hi, Leonel. Thank you for taking me to the right way.

It works well:

     rows = db(~db.persons.id.belongs(db()._select(db.tbl.person))).select()

And I don't use dots in table names.