You can do
db().select(db.company.ALL,db.address.ALL,left=db.address.on(
(db.address.company==db.company.id)&(db.address.zipcode<90000)&
(db.address.zipcode>70000)))
On Apr 6, 2:16 am, annet <jmverm...@xs4all.nl> wrote:
> I am working on the implementation of a search on zip-code.
>
> The model:
>
> db.define_table('company',
> SQLField('name', length=54, default='', notnull=True),
> SQLField('kvk_nummer', length=8),
> SQLField('subdossiernummer', length=4, default='0000'),
> SQLField('status', length=1, default='0'),
> migrate=False)
>
> db.define_table('address',
> SQLField('company', db.bedrijf, default='', notnull=True),
> SQLField('street', length=42),
> SQLField('number', length=6),
> SQLField('zip_code_digits', type=integer, length=4),
> SQLField('zip_code_letters', length=2),
> SQLField('city', length=42),
> migrate=False)
>
> db.define_table('zipcoderegions',
> SQLField('region', type=integer, length=2, default='',
> notnull=True),
> SQLField('min', type=integer, length=4, default='', notnull=True),
> SQLField('max', type=integer, length=4, default='', notnull=True),
> migrate=False)
>
> Values in the zipcoderegions table are:
>
> 73 7300 7399
> 80 8000 8099
> 91 9100 9199
>
> In SQL the query reads like:
>
> SELECT company.id, company.name, company.status, address.street,
> address.city
> FROM company
> INNER JOIN address ON company.id=address.company
> WHERE address.zip-code_digits BETWEEN zipcode.min AND zipcode.max
> ORDER BY company.status, company.name;
>
> The problem is I have no idea how the build the form in which the user
> selects a zip-code region, e.g. 73 then retrieve the min (7300) and
> max (7399) from the zipcoderegions table and insert them in the
> query.
>
> I hope one of you can point me in the right direction to get this
> working.
>
> Kind regards,
>
> Annet
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
